Can Worksheet_Change be used to back-out (undo) a change?

[PoggiPJ]

I need to test the content of a time-stamped audit control cell prior to allowing the user to change anything in a given range. If the audit control cell contains a time stamp, then the user should be given a chance to proceed - accepting the data entry change and erase the time stamp, or cancel the change, restore the original data, and retain the time stamp.

My thought was to place code in the Worksheet_Change event on that sheet where the data entry range is located, like this...

Private Sub Worksheet_Change(ByVal Target As Range)
    If DMSSisLocked Then
        Exit Sub
    else
        'accept the change and perform operations against the target
    End If
End Sub

The DMSSisLocked function would test the audit control cell

Public Function DMSSisLocked()
Dim ans As Variant
Dim LockedState As Boolean

If Configuration.Range("cfgDMSSLockState") = True Then
     'This deal is locked, and no further edits should have been made.
     ans = InputBox(OfferText & "If you proceed, your DMSS quote will no longer show as Locked and the timestamp will be removed" _
                & vbCrLf & vbCrLf & "Do you still want to continue? Please answer Yes or No", "Warning: Read Before Proceding!")
           If LCase$(Application.Trim(ans)) <> "yes" Then
               Cancel = True
               DMSSisLocked = True
               Exit Function 'Drop right out of the subroutine
           Else
               Configuration.Range("cfgDMSSLockState").Value = False
               MsgBox "DMSS is Unlocked"
               DMSSisLocked = False
           End If
                
       Else
           DMSSisLocked = False
       End If
End Function

but the Worksheet_Change event doesn't appear to offer a Cancel option (like the SaveAs does). So regardless of what the user answers, the data entry cell retains the changed value.

Is there a way to restore the original content of the changed cell if the user declines?

 

[xladept]

Hi PoggiPJ,

You could try something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Static T As Range
If Target.Address = "A1" Then 'The address of the cell involved
    If DMSSisLocked Then
        Target = T: Exit Sub
    Else
    T = Target
        'accept the change and perform operations against the target
    End If: End If
End Sub

 

[Andrew Poulsom]

Have you tried?

Application.Undo

 

[PoggiPJ]

Sorry, was interrupted by a crisis - trying it today

 

[PoggiPJ]

Saving the Target did not work. Seems that by the time control is passed into the Worksheet_Change routine, the original value is already "gone".

The Application.Undo did work fine.

Thank you!