Last Sheet Edit TimeStamp

[W1ART]

Hi All,
I think this is a common question, but can't find the answer. I am working with 2013 on a workbook with about 30 sheets/tabs. I would like a timestamp cell in each sheet which will trigger only when another cell in that same sheet is changed. Each sheet timestamp must independent of each other (no interaction between them). I have tried using the Now() function in VBA, and while my function seems to be sheet independent for a while, all the timestamps eventually get re-synchronized to the current time. My function looks like this:

Public Function LastSheetEdit(c As Range)
LastSheetEdit = Now()
End Function

Any help would be appreciated.
Thanks,
ART

 

[Scott T]

This code will run and update the timestamp when any cell is changed.
This needs to go into the worksheet module of each sheet you want it to run on.

right click on the tab and select view code Paste the code below into the VBA editor.

You will see your sheets listed in the project explorer double click on a sheet or right click and select view code to open the sheet module and past the code there until all your sheets have the code.

Change A1 to the cell where you want the timestamp. change the format to what you want

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("A1") = Format(DateTime.Now(), "m/d/yyyy h:mm")

Application.EnableEvents = True

End Sub

 

[Joe4]

Welcome to the Board!

The issue is that you have made it a function, and function values can change when there is a recalc or refresh.
Instead of using a Function, you need to use a Procedure to hard-code the current date/time value in the cell.

 

[Joe4]

This needs to go into the worksheet module of each sheet you want it to run on.

You can avoid having to place it in every sheet by placing the following version of that code in the "ThisWorkbook" module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    ActiveSheet.Range("A1") = Format(DateTime.Now(), "m/d/yyyy h:mm")
    Application.EnableEvents = True
End Sub

 

[Scott T]

You can avoid having to place it in every sheet by placing the following version of that code in the "ThisWorkbook" module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    ActiveSheet.Range("A1") = Format(DateTime.Now(), "m/d/yyyy h:mm")
    Application.EnableEvents = True
End Sub

I thought about a save event but the original post stated that the timestamp should be when a cell changed and on each sheet.

 

[Joe4]

I thought about a save event but the original post stated that the timestamp should be when a cell changed and on each sheet.

Look again, I am not using the Save event.

Try it out and see, it does precisely that (when any cell in any sheet is updated, it will update cell A1 on that same sheet with the date/time stamp).

Sadly, I cannot take credit for this idea. I used to do it the same way you posted, then I saw someone use this "trick" some time ago.

 

[W1ART]

Scott,
TU very much. Yes, your code works great! I've tested it and so far no issues...

Joe,
TU for letting me know why using a function is a bad idea in this case.
I also, wondered about using Scott's code for the whole workbook. I like the idea but am not sure what to do exactly, as I already have workbook code which looks like this:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
Target.PasteSpecial xlPasteValues
Application.CutCopyMode = True
End Sub

Can I just add it in?

 

[Joe4]

You have "Workbook_SheetSelectionChange" event procedure code.
What I posted is "Workbook_SheetChange" event procedure code.
Notice the difference in names. They are different.

Just paste the code that I gave you underneath the code that you currently have in the "ThisWorkbook" module, and you won't need any of the individual Worksheet_Change procedures.

 

[W1ART]

Joe, Yes pasting your code following the existing works fine. Thanks!

 

[Joe4]

You're welcome!
Some advantages to that method include:
- if you add any new sheets, it will work on them too automatically without having to add any new code
- if you ever need to update the code, you only need to update it in one place, instead of in a bunch