Excel VBA get value before Worksheet_Change event

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
Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *