Showing date of last Revision

[Dave McDonald]

I want to have a cell where I show the date the last
change was made to the worksheet (like a last revision
date). I think this is pretty easy to do if you know how,
but I don't.

Dave McDonald

 

[Frank Kabel]

Hi
try the following UDF:

Function Last_Save_Time()
Last_Save_Time = ActiveWorkbook.BuiltinDocumentProperties("Last
save time")
End Function

In your cell enter
=Last_Save_Time()

 

[Ron de Bruin]

ActiveWorkbook.BuiltinDocumentProperties
Is not working correct in Excel 97

Read this from Tom Ogilvy

Note that this property is not maintained in xl97. However, if the workbook
is created/used in both xl97 and later versions, then when used in the
later versions and saved the property is maintained but when used in xl97
and saved, the property is not altered. The time it was last saved in the
later version will be retrieved if this property is called. If created and
used exclusively in xl97, calling this property will raise an error. You
can use the beforesave event to update this property in xl97 as a
workaround. Just test the version of excel and if xl97, update the
property.

another option is this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Sheets("Sheet1").Range("a1").Value = Date
End Sub

Right click on the Excel icon next to File in the menubar
Choose view code
Paste this event there
Alt-q to go back to Excel

If you save the file the date will be placed in A1

 

[Frank Kabel]

Hi Ron
good point

 

[Ron de Bruin]

Hi John

You can use this event in the Thisworkbook module
This will run when you print a sheet(s) in your workbook

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In ThisWorkbook.Worksheets
wkSht.PageSetup.RightFooter = "&8Last Saved : " & _
ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
Next wkSht
End Sub