1lineatatime
05-21-2020, 12:22 PM
Hello, this is my first post since I've joined this forum! I read the rules but if i'm missing anything in this post, please let me know so I can improve next time. So, in excel, I have been designing a workbook that automatically places a timestamp in a particular column when an 'x' is placed in a particular row. This timestamp insertion for sheet1 works great! However, the problem I have been running into is that if a user accidentally double clicks a cell or places an 'x' when they did not mean to, the timestamp adjusts automatically and there is no way to retrieve the previous timestamp (since excel does not natively store changes made by vba events in the undo list). Of course, I can simply exit without saving and then open the document again. But, my goal is to code in an option to restore the previous timestamp. I know there are multiple ways to go about this, but any assistance with getting my code right would be great. I am on windows 10 using Excel 2013. An additional note: There may be a way to use application.undo or scan each row using worksheet_calculate, but I am new to VBA and am not sure how to incorporate that into what I already have. Thank you very much for any assistance. The attached code is what i have thus far:
Private Sub Worksheet_Change(ByVal Target As Range)
'Automatic TimeStamp Insertion
Dim xRInt As Integer
Dim xDStr As String
Dim xFStr As String
On Error Resume Next
xDStr = "D:R" 'Data Columns that contain possible 'x'(s)
xFStr = "C" 'Timstamp Column
If (Not Application.Intersect(Me.Range(xDStr & ":" & xDStr), Target) Is Nothing) Then
xRInt = Target.Row
Me.Range(xFStr & xRInt) = Format(Now(), "mm/dd/yyyy hh:mm:ss")
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Calculate
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'Automatic TimeStamp Insertion
Dim xRInt As Integer
Dim xDStr As String
Dim xFStr As String
On Error Resume Next
xDStr = "D:R" 'Data Columns that contain possible 'x'(s)
xFStr = "C" 'Timstamp Column
If (Not Application.Intersect(Me.Range(xDStr & ":" & xDStr), Target) Is Nothing) Then
xRInt = Target.Row
Me.Range(xFStr & xRInt) = Format(Now(), "mm/dd/yyyy hh:mm:ss")
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Calculate
End Sub