MrExcel Publishing
Your One Stop for Excel Tips & Solutions

auto date last time saved

Posted by Brandie Hanson on October 11, 2001 1:36 PM

I want set a footer like in word that will auto install that last date saved. This information is in proerties but I want it in a footer.

Posted by Steve Martindale on October 12, 2001 1:11 AM

I think the best way to do this id to use a macro in the This Workbook section of the VB editor.

Assuming you have no knowledge of VB programming, here’s how to do it in words one syllable (more or less)

With your workbook open, open the Visual Basic Editor by pressing Alt F11.

On the left you should see a list of VBA Projects, Double-click on the one you want if there isn’t a list of one or more folders beneath it. If necessary, double-click on Microsoft Excel Objects to get a list with shett1, sheet2, etc, and at the bottom, ThisWorkbook. Double-click this to open the code editing window for this object.

Copy the following into the window, substituting appropriate figures for row and column:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cells(Row, Column) = Date
End Sub
The spaces, or, more important, the lack of them matter, but the editor will take care of capitalisation.
This will put the date in. If you want the date and time, add “+ Time” to the middle line. A slight disadvantage of this is that if someone uses Save As and cancels the save, the date will be changed.

If you have any problems, email me.