VBA - date, time and author for each worktab

Stewie

New member
Joined
Aug 6, 2012
Messages
7
Reaction score
0
Points
0
Hi,
i have a spread sheet with over 20 worktabs and needs to show the date, time and author every time each worktab is modified in anyway.

I am currently using the code shown below in each worktab but this is only showing date, time etc on one tab at a time even if multiple tabs have been modified.

Code:
Private Sub Workbook_Open()
Dim last_auth As String
Dim last_save As String
last_auth = ThisWorkbook.BuiltinDocumentProperties("Last Author")
last_save = ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
ThisWorkbook.BuiltinDocumentProperties("Last Author") = last_auth
ThisWorkbook.BuiltinDocumentProperties("Last Save Time") = last_save
Range("E2") = ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
Range("E3") = ThisWorkbook.BuiltinDocumentProperties("Last Author")
End Sub

can you advise on this please?
 
Last edited by a moderator:
Put this in ThisWorkbook code module

Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Application.EnableEvents = False
    
    With Sh
    
        .Range("E2").Value = Now
        .Range("E2").NumberFormat = "dd mmm yyyy hh:mm:ss"
        .Range("E3").Value = Environ("Username")
    End With


    Application.EnableEvents = True
End Sub
 
Put this in ThisWorkbook code module

Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Application.EnableEvents = False
    
    With Sh
    
        .Range("E2").Value = Now
        .Range("E2").NumberFormat = "dd mmm yyyy hh:mm:ss"
        .Range("E3").Value = Environ("Username")
    End With


    Application.EnableEvents = True
End Sub

Hi Bob - many thanks for the quick reply - is there anyway of using "Last Author" instead of "username" so it shows my name "Stephen Jones" instead of my actual login joness66?
 
Use

Application.Username
 
Hi Bob, not sure if I have this correct but this does not work, no author name is showing?


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False

With Sh

.Range("E2").Value = Now
.Range("E2").NumberFormat = "dd mmm yyyy hh:mm:ss"
.Range("E3").Value = Environ("Application.Username")
End With

Application.EnableEvents = True
End Sub
 
No, just

.Range("E3").Value = Application.Username
 
Hi Bob, worked like a dream! many thanks for your help, much appreciated!
 
Back
Top