Modified Date of a Worsheet

  • Thread starter Thread starter Bob Phillips
  • Start date Start date
B

Bob Phillips

Excel doesn't save the change time of a sheet. If you want it you will have
to maintain it yourself, maybe just trap the Worksheet_Change event.
 
At the risk of sounding utterly helpless,
How do I trap the worksheet change event?

Thanks Bob
 
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Names.Add Name:="_timestamp", RefersTo:=Now
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
 
=_timestamp

and format as required.

Note, it will not automatically update, you will need to force a
recalculation every time you need the value.
 
Bob, then the OP could as well input =NOW() into A1. No need to trap the
Worksheet_Change event. I tweaked your code and came up with:
Private Sub Worksheet_Change(ByVal Target As Range)
[A1] = Now()
End Sub

Needs no recalculation and changes the date in A1 whenever data on the
worksheet are changed.

Cheers,

Joerg Mochikun
 
Hi

I am trying to display the last modified date of a worksheet in a workbook.
Googled it and came up with:

Function MyStamp()
Application.Volatile
MyStamp = FileDateTime(ThisWorkbook.FullName)
End Function

The thing is that this applies to the WHOLE workbook and not the individual
sheet, which I don't need.
Tried to change the word Workbook in the VBA into Worksheet -- didn't work.

Can anybody help?
 
True, but my method gives them a variable that can be used throughout the
workbook, tested or combined in a function

--
__________________________________
HTH

Bob

Joerg Mochikun said:
Bob, then the OP could as well input =NOW() into A1. No need to trap the
Worksheet_Change event. I tweaked your code and came up with:
Private Sub Worksheet_Change(ByVal Target As Range)
[A1] = Now()
End Sub

Needs no recalculation and changes the date in A1 whenever data on the
worksheet are changed.

Cheers,

Joerg Mochikun

Bob Phillips said:
=_timestamp

and format as required.

Note, it will not automatically update, you will need to force a
recalculation every time you need the value.

--
__________________________________
HTH

Bob
Click to expand...
Click to expand...
 
Thanks! That One Guy, but how do I put the value on a specific cell, say
A1?

Thanks~!

Gerard
 
Back
Top