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:

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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
 

Stewie

New member
Joined
Aug 6, 2012
Messages
7
Reaction score
0
Points
0
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?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Use

Application.Username
 

Stewie

New member
Joined
Aug 6, 2012
Messages
7
Reaction score
0
Points
0
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
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
No, just

.Range("E3").Value = Application.Username
 

Stewie

New member
Joined
Aug 6, 2012
Messages
7
Reaction score
0
Points
0
Hi Bob, worked like a dream! many thanks for your help, much appreciated!
 
Top