Results 1 to 3 of 3

Thread: Automatic Timestamp Insertion Application.Undo

  1. #1

    Automatic Timestamp Insertion Application.Undo

    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)
    End Sub
    Last edited by Paul_Hossler; 05-21-2020 at 01:32 PM. Reason: Removed 2 extra pair CODE tags

  2. #2
    VBAX Wizard
    Apr 2007
    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)


    Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums

  3. #3
    Thank you very much Paul!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts