Insert "last saved" date into footer

[lafontainek]

Hello all
all i can find is the =NOW() option, but I can't use NOW, i need date of last save.
i need the footer to read "Dave Evans Last Saved 3/8/2011" and the date to change each time it's saved.
Please and thank you!

 

[VoG]

Hi. Try this in the ThisWorkbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet1").PageSetup.LeftFooter = Environ("username") & " Last saved " & Format(Now, "mmmm dd yyyy at h:mm:ss AM/PM")
End Sub

 

[lafontainek]

Could you explain "ThisWorkbook module" ?

 

[VoG]

Hopefully

Press ALT + F11 to open the Visual Basic Editor.

In the Project window (normally top left) double click ThisWorkbook.

Copy the code and paste it into the white space on the right.

Press ALT + Q to close the code window.

Check the result by saving the workbook then printing Sheet1.

 

[lafontainek]

when i try to save:
Runtime error '9'
Subscript out of range.

i click debug and it highlights this line in yellow:
Sheets("Sheet1").PageSetup.LeftFooter = Environ("username") & " Last saved " & Format(Now, "mmmm dd yyyy at h:mm:ss AM/PM")

?

 

[VoG]

Change Sheet1 to the name of the sheet

Sheets("Sheet1").PageSetup.LeftFooter = Environ("username") & " Last saved " & Format(Now, "mmmm dd yyyy at h:mm:ss AM/PM")

 

[lafontainek]

OK that works! But the name isnt listed.
I tried replacing "username" with Dave Evans but it doesnt appear.

 

[VoG]

If you are using Windows then that should insert the Windows logon name.

Alternatives:

Sheets("Sheet1").PageSetup.LeftFooter = Application.UserName & " Last saved " & Format(Now, "mmmm dd yyyy at h:mm:ss AM/PM")

Sheets("Sheet1").PageSetup.LeftFooter = "Dave Evans" & " Last saved " & Format(Now, "mmmm dd yyyy at h:mm:ss AM/PM")

 

[lafontainek]

Hmm... i must be doing something wrong, it's not appearing.
I went into print preview and did custom footer just to add his name, but i notice the date and time arent changing each time i save it... it still reads 11:27am even though i've saved it since then, even with a new document name.

 

[VoG]

It works here.