Automatic Saved Date

T

tommy20

This sounds awfully basic, but for the life of me, I can't figure out
how to do it.

I would like the date at the top of my spreadsheet to reflect the last
save date for the file. Isn't there a function that will handle this?
 
J

Jim Cone

There is no built-in function to do this.
A user designed function (UDF) is necessary.
'--------------------
'Place the following code in the "ThisWorkbook" module...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.OnTime Now, ThisWorkbook.Name & "!TimeSaved"
End Sub

'Place the following code in a standard module...

Sub TimeSaved()
Dim strPath As String
strPath = ActiveWorkbook.Path
Range("B4").Value = "Last saved: " & FileDateTime(strPath)
End Sub
'--------------------

Every time the workbook is saved, the saved date and time will appear
in Cell B4 on the currently active sheet.

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


in message...
This sounds awfully basic, but for the life of me, I can't figure out
how to do it.
I would like the date at the top of my spreadsheet to reflect the last
save date for the file. Isn't there a function that will handle this?
 
T

tommy20

UDF, eh? Well, that'll sure be unknown territory for me. I'll give i
a shot.

Thanks
 
J

Jim Cone

The code I gave you has some problems when the
workbook is closed. Also, technically it is not a
user designed function. The following code placed
in a standard module should do what you want.
It will update every time the workbook is recalculated.
To use it, in a cell just enter ...
= timesaved()

Jim Cone

'--------------------------
Function TimeSaved()
Application.Volatile
Dim strPath As String
strPath = ActiveWorkbook.Path
TimeSaved = "Last saved: " & FileDateTime(strPath)
End Function
 
Top