PDA

View Full Version : [SOLVED] Automatic Timestamp Insertion Application.Undo



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

Paul_Hossler
05-21-2020, 01:31 PM
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
05-26-2020, 03:34 AM
Thank you very much Paul!

samll
11-19-2020, 07:19 AM
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