Showing date last saved

C

Colin Macleod

I want one of the cells on my worksheet to show the date the file was last
saved on - and to update automatically.
What's the best way to do this?
Thanks

Colin

--
 
A

Alan

You need code to do this,

One way to do it, copy this code into the this 'ThisWorkbook' window in the
VBA editor.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Sheet1.Range("A1").Value = Date
Sheet1.Range("A2").Value = Time
End Sub

This will put the date saved into A1 and the time saved into A2. To have the
date and time in one cell use

Sheet1.Range("A1").Value = Now

To do this, hit Alt and F11 together to display the VB editor, on the left
of the new window you should see 'This Workbook' Click this to open the
window, copy and paste, hit Alt and F11 again to close the VB editor, save
the file.
Adjust the cell references to suit and format the cells to suit as well,
Regards,
Alan.
 
G

Gord Dibben

Colin

Copy/paste this Function to a module in your workbook.

Function DocProps(prop As String)
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

Usage in a cell is: =DOCPROPS("last save time")

Format the cell as Date.

You can also use "last author" or "creation date"

If not familiar with macros and VBA, visit David McRitchie's website on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP
 
Top