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.
--

thanks so much!
veek

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.




"veek" wrote:

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.
--

thanks so much!
veek

I think that the second Application.EnableEvents should be TRUE

Set modLocation = Nothing
Application.EnableEvents = True
End Sub

--
Regards,

OssieMac


"JLatham" wrote:

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.




"veek" wrote:

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.
--

thanks so much!
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?
--

thanks so much!
veek


"JLatham" wrote:

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.




"veek" wrote:

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.
--

thanks so much!
veek

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.

--
Regards,

OssieMac


"veek" wrote:

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?
--

thanks so much!
veek


"JLatham" wrote:

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.




"veek" wrote:

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.
--

thanks so much!
veek

Thanks for catching that - definitely a problem creator.

My sincere apologies to the OP.

"OssieMac" wrote:

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.

--
Regards,

OssieMac


"veek" wrote:

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?
--

thanks so much!
veek


"JLatham" wrote:

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.




"veek" wrote:

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.
--

thanks so much!
veek

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 ViewRefresh in Windows Explorer.

Date will change to current.

Close the workbook without save and ViewRefresh in WE.

Note date changes back to original.


Gord Dibben MS Excel MVP




On Thu, 24 Jul 2008 14:43:02 -0700, veek
wrote:

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.