"last modified" timestamp function in excel 2003

[veek]

i need to insert a function for a cell that captures the last time the file
was *modified* (not last accessed or last saved). i am sure there exists
such a thing, but was surprised to not find it in the list of built-in date &
time functions.

 

[JLatham]

If you really want to keep up with latest time that the workbook was
modified, then you need to work through the Workbook_SheetChange() event
handler. With code similar to this (change the sheet name and cell address
to point to where ever you want this time stamp to appear)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim modLocation As Range
Application.EnableEvents = False
Set modLocation = Worksheets("Sheet1").Range("B2")
modLocation = Now()
Set modLocation = Nothing
Application.EnableEvents = False
End Sub

This page will help you get the code into the proper location in your
workbook:
http://www.jlathamsite.com/Teach/WorkbookCode.htm

Hope this helps.

 

[Gord Dibben]

There is no "modified date" to grab.

When you open a workbook the modified date changes to current.

If you close the workbook with no changes or save, the modified date will
revert to last modified date which is last saved date.

Open Windows Explorer and find a workbook saved before today and note the
modified date,

Now open that workbook and View>Refresh in Windows Explorer.

Date will change to current.

Close the workbook without save and View>Refresh in WE.

Note date changes back to original.


Gord Dibben MS Excel MVP

 

[OssieMac]

I think that the second Application.EnableEvents should be TRUE

Set modLocation = Nothing
Application.EnableEvents = True
End Sub

 

[veek]

jlatham - thank you so much for offering assistance. i followed the
user-friendly instructions in your link below but i'm not getting any
timestamp in the field i specified - so i know i am doing something wrong.
the worksheet is named "Communications-Milestones" and the cell address is C6
so the code i entered looks like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim modLocation As Range
Application.EnableEvents = False
Set modLocation = Worksheets("Communications-Milestones").Range("C6")
modLocation = Now()
Set modLocation = Nothing
Application.EnableEvents = False
End Sub

I tried it inside of the default _Open event code and also I tried it after
removing the _Open code - and cannot get it to work either way.

I had originally just set the Now() function for field C6 but wouldn't that
just capture the current time and not the time of last mod?

 

[OssieMac]

Firstly, as per my previous post, there is an error in the code. (I am sure
the error was inadvertant and we all make them). Change the second last line
of code Application.EnableEvents = False
to
Application.EnableEvents = True

When events have been turned off, if they are not turned on again in code,
then they remain off for the remainder of the Excel session and no events
will run until events is turned on again.

If events are turned off and not turned back on again either due to code
error or a failure of the code before they are turned back on then it is then
necessary to run a separate sub on its own like the following to turn them
back on again:-

Sub Reset_Events()
Application.EnableEvents = True
End Sub

Secondly, in the VBA editor, did you select ThisWorkbook in the VBA Project
Explorer and place the code in there?

I tested the code (with my modification) and it works.

 

[JLatham]

Thanks for catching that - definitely a problem creator.

My sincere apologies to the OP.