Automatic Timestamp Insertion Application.Undo

[1lineatatime]

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

[Paul_Hossler]

I'd do it this way

BTW, if someone puts an X into D2 and later an X in R2, the original timestamp in C2 will be replaced

Option Explicit


'Automatic TimeStamp Insertion
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    
    Set r = Target.Cells(1, 1)
    
    If r.Column < 4 Or r.Column > 18 Then Exit Sub
    If UCase(r.Value) <> "X" Then Exit Sub
    
    Application.EnableEvents = False    '   <<<<< Important
    r.Value = "X"   '   just in case
    Me.Cells(r.Row, 3).Value = "'" & Format(Now(), "m/dd/yyyy h:mm:ss am/pm")
    Application.EnableEvents = True    '   <<<<< Important


End Sub


'not needed
'Private Sub Worksheet_SelectionChange(ByVal Target As Range)

[1lineatatime]

Thank you very much Paul!

[samll]

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

I was looking all over stack overflow for this thing and strongly find this here
thank you mate