Forum Discussion
Timestamp using VBA based on specific cell changes
Hi experts,
Hope you are well!
I am trying to get VBA to update a cell in one column with a timestamp of when another cell changes.
The attached code works well but when I use a filter on the spreadsheet, the code updates the timestamp to the current time when the actual cell has not been clicked or changed.
Is there anyway to better this code?
Function MyTimestamp(Reference As Range)
If Reference.Value <> "" Then
MyTimestamp = Format(Now, "dd-mm-yyyy hh:mm:ss")
Else
MyTimestamp = ""
End If
End Function
See attached example
What I would add is that ideally I would like this timestamp column to eventually consider any data updates in multiple columns. For example if any data changes in Cols A-H, column I is the timestamp of last change. Not sure if this could be added?
Thank you in advance
Matt
I adjusted the cell formatting again to user-defined MM/DD/YYYY hh:mm, maybe it's due to the different date settings.
Attached is the file with the customized formatting.
7 Replies
Attached is a modified version of your code.
At the same time, I add two files with an example as in the code and an example file with a timestamp in the cells as a comment.
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub If Target.Count > 1 Then Exit Sub 'Editing multiple lines is caught If Target = "" Then Target.Offset(0, 1).ClearContents Else Target.Offset(0, 1) = CDate(Format(Now, "dd.mm.yyyy")) End If End SubMy answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
- Hi, thank you for this.
The range is the best option for me, but for some reason I get the debug error 😞- Excel version, operating system, storage medium, ending of the saved file, etc.
All this information is needed to have a clear picture of what and why something might not work.
On two laptops where I tried the files (both with Excel 2016) it worked without any problems.
