This Excel VBA tutorial explains how to get value before Worksheet_Change event.
Problem with getting value before Worksheet_Change event
When we use Worksheet_Change event, the event is triggered after we edit a Cell (even no value is changed). For example, when A1 value is 10, we double click range A1 to change value to 20. As we press enter, Worksheet_Change event is triggered. As you can see, the old value 10 is not stored anywhere, but sometimes we do need the old value to do calculation.
Method 1 – Get value before Worksheet_Change event
The first method of getting value before Worksheet_Change event is to use Worksheet_SelectionChange event. Worksheet_SelectionChange event is triggered when you click on a new cell, you can save the cell value the moment you click on the cell as old value.
Because Worksheet_Change event and Worksheet_selectionChange event are in separate Private Sub within the same worksheet, you should declare a variable for the old value at worksheet level, so that all Sub can access the old value.
Dim oldValue As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
oldValue = Target.Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'do something with oldValue...
End Sub
For example, you need to capture the value before change in A1. the range value was changed from 10 to 20.
In the above code, as you hit A1, Worksheet_SelectionChange is triggered and store 10 in oldValue. After you change a value to 20, you can still get the old value 10 from the result of Worksheet_SelectionChange.
Method 2 – Get value before Worksheet_Change event
The second method of getting value before Worksheet_Change event is to use Application.Undo Method. This method revert the value of A1 from 20 to 10.
Private Sub Worksheet_Change(ByVal Target As Range) Dim OldValue As Variant Application.EnableEvents = False Application.Undo OldValue = Target.Value Application.Undo Application.EnableEvents = True 'do something with oldValue... End Sub
The first Undo Method change the value back to 10, the second Undo changes the value to 20.
Example – Trigger Macro when Value Changes
Below is a solution I copied and pasted from Microsoft Community that was answered by me.
Question
How can you instruct a macro to run when a certain cell changes?
For example, as soon as text in cell A1 changes, a macro is triggered.
Answer
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
Dim OldValue As Variant
Application.EnableEvents = False
Application.Undo
OldValue = Target.Value
Application.Undo
Application.EnableEvents = True
If OldValue <> Target.Value Then
'Your Macro
End If
End If
End Sub
OR
Dim oldValue As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
oldValue = Target.Value
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
If Target.Value <> oldValue Then
'Do something
End If
End If
End Sub
