Showing when a file was updated

  • Thread starter Thread starter Mikey B
  • Start date Start date
M

Mikey B

I am trying to figure out how to have a cell display when a file was last
updated. Is there a way to make it show the most current date that any cell
has been updated. I have tried using =TODAY() but that doesn't change with
the most recent change.
 
Not without some VBA event code.

Would having the last save time/date entered in a cell suffice?

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

=DOCPROPS("last save time") entered in acell.


Gord Dibben MS Excel MVP
 
This is for a single worksheet. We will use cell A1. We will capture the
last time any cell is updated (directly, not formula)

First format A1 as Custom > m/d/yyyy h:mm:ss
and then enter this tiny worksheet event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Cells(1, 1).Value = Now
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

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

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm
 
Back
Top