How to put save date in the header (or footer)

[GypsyHarper]

I would like to enter a date in the header (or footer) of my Excel
spreadsheet. However, I want it to be the date the file was last saved, not
today's date. Is there a way to do this?

 

[Gord Dibben]

Sub Last_Saved_Footer()
'Private Sub Workbook_BeforePrint(Cancel As Boolean) 'alternate method
Dim wkSht As Worksheet
For Each wkSht In ThisWorkbook.Worksheets
wkSht.PageSetup.RightFooter = "&8Last Saved : " & _
Format(ThisWorkbook.BuiltinDocumentProperties("Last Save Time"), _
"yyyy-mmm-dd hh:mm:ss")
Next wkSht
End Sub


Gord Dibben MS Excel MVP

 

[Ron de Bruin]

Hi GypsyHarper

See
http://www.rondebruin.nl/print.htm#Last

 

[Kevin B]

Press <Alt> + F11 to open the Visual Basic Editor.

In the upper left, double click the ThisWorkbookIcon to open the workbook
module.

In the code window on the right, drop down the left combo-box at the top and
change selection to WORKBOOK, and then drop down the right combo-box at the
top and change the selection to BEFORESAVE. Enter the code that appears
betwen the Private Sub Line and the End Sub Line.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
With ws.PageSetup
.LeftFooter = "Last Save Time: &T"
.RightFooter = "Last Save Date: &D"
End With
Next ws

Set ws = Nothing

End Sub

Everytime you save the workbook the save time is posted to the left foooter
of each worksheet and the save date to the right footer of each worksheet.

Save the file to test drive it and then do a print preview.

 

[GypsyHarper]

The code worked splendidly in my test file. But when I went to put it in the
file I actually want to use it in, the "ThisWorkbook" icon does not appear,
even after I saved a copy as a Macro Enabled file (I'm using Excel 2007, by
the by). Any ideas?

 

[Ron de Bruin]

the "ThisWorkbook" icon does not appear,

???

If you not see it see this page
http://www.rondebruin.nl/code.htm

 

[GypsyHarper]

Yes, I see the project explorer. The project has no folders underneath it -
it's not just collapsed. There's no plus sign, and when I double click on
the project is says it's locked.

 

[Ron de Bruin]

I think your VBA project is password protected

Does it ask you for a password ?

 

[GypsyHarper]

Nope, doesn't ask for a password. It just says project locked. I think the
file was brought over into Excel from an older version of Access at one
point. Maybe that has something to do with it?

 

[Ron de Bruin]

Ok

Open the file in Excel 97 and remove the password

 

[Ron de Bruin]

Oops

I think you try to change the VBA in 97 now
Unprotect the VBA in a higher version of Excel

Then change the code and set the protection again in Excel 97
Now you can unlock your project in every Excel version