Search code examples
excelworksheet-functionvba

Detecting what value was in a cell prior to a change

Is there a way to detect what a cell use to contain before it was changed. I'm trying to perform actions based on what the previous value was in a cell. I know there's Worksheet_Change but the Target it uses is the new value.

Solution

  • You can undo the change, populate the undone value to a variable then redo the change like so:

    Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldValue As Variant, NewValue As Variant
NewValue = Target.Value
Application.EnableEvents = False
Application.Undo
OldValue = Target.Value
Target.Value = NewValue
Application.EnableEvents = True
MsgBox OldValue
End Sub