Programmatically responding to worksheet changes

Excel has built in event handlers that track how a user interacts with a worksheet.

One of these, the Worksheet_Change event handler, fires when a change is made to a worksheet cell.

You can use it to make calculations, to track when changes are made and even to undo unwanted changes.

We’ll show you how.

Making calculations

To see how to use the Worksheet_Change event handler to make calculations, we’ll create a simple conversion calculator. Start by typing the words ‘Celsius’ and ‘Fahrenheit’ into cells A1 and B1 of a new sheet.

Type this into the code dialog for that sheet:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target = Cells(2, 1) Then
Cells(2, 2) = Cells(2, 1).Value * 9 / 5 + 32
ElseIf Target = Cells(2, 2) Then
Cells(2, 1) = (Cells(2, 2).Value - 32) * 5 / 9

End If
End Sub

Now, when you type a value into cell A2 or B2, it will be converted to the other temperature scale and the result will appear in the appropriate cell in the sheet.

Undo changes

Another use for the Worksheet_Change event handler is to track and undo unwanted changes to data.

This example undoes changes made to any cell in column B of the current sheet.

To see it at work, type this into a sheet’s code dialog:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End Sub

Now any changes to any cell in Column B will be automatically and immediately undone — the Application.Undo statement takes care of this.

The Application.EnableEvents statement stops other events from firing while the code runs. This avoids an unwanted cascading effect where the macro itself would trigger the event handler to fire while the current code is still running.

Make sure that, whenever you turn EnableEvents off in your code, you later turn it back on again — otherwise it will remain off.

You can also use the Worksheet_Change event handler to track when a worksheet was last updated.

On a new sheet, enter this into the code dialog:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 2 Then
Range(“A1”).Value = “Worksheet Last Changed: “ & Date
ElseIf Target = Cells(1, 1) Then
Application.Undo
End If
Application.EnableEvents = True
End Sub

The date of any change to the worksheet is recorded in cell A1 and any attempts by the user to change that cell will be undone.


How to enter code into a sheet

step1Step 1

To locate the code dialog for a sheet in a workbook, right click the sheet name and choose View Code.

step2Step 2

Type the event handler code into the code window and choose ‘File > Close and Return to Microsoft Excel’ to return to your worksheet.

step3Step 3

Test the code by entering a value into the current sheet.

Each of these macros are limited in scope to the sheet that contains the code and they don’t work with any other sheet.