Last Sheet Edit TimeStamp

W1ART

New Member
Joined
Dec 21, 2017
Messages
3
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,615
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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
 
Last edited:

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,615
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

You can avoid having to place it in every sheet by placing the following version of that code in the "ThisWorkbook" module.
Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

W1ART

New Member
Joined
Dec 21, 2017
Messages
3

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 21, 2017
Messages
3
Joe, Yes pasting your code following the existing works fine. Thanks! :cool:
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,912
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top