How to Tell if a Cell Changed with VBA
July 04, 2017
There are times when you want to run a macro when a cell changed. Maybe you want to do some special calculation when a cell changes. Or maybe you’d like to change the font to all uppercase. Or maybe you want to present your users with more meaningful data validation messages. Whatever the case may be, you can use Excel’s Worksheet_Change event in VBA to help accomplish your automation needs.
What is a Worksheet_Change Event?
The Worksheet_Change
event is a special event that happens in Excel when a
cell (or multiple cells) has changed in a specific worksheet. This includes when
a cell is created, updated, or deleted.
This does not include changes like:
- Formatting changes (font size, cell size, font/cell color, conditional formatting, etc.)
- A cell changed because the calculation was updated (this is a different event
called the
Worksheet_Calculate
event) - Selecting a cell (this is another event called
Worksheet_SelectionChange
event)
How do I use this Event?
Open up the VB Editor (Alt+F11
in Windows or Tools->Macros->Visual Basic
Editor on Mac). In the Project window, you’ll see where the workbook and
worksheets are:
Double-click on the Sheet1 object and paste the following code:
Option Explicit
Public Sub Worksheet_Change(ByVal target As Range)
Debug.Print "Something changed in cell " & target.Address(0, 0)
End Sub
This is a very basic example of how to start using the Worksheet_Change
event.
This method is what’s called an Event Handler, which simply means that “this is
the method I want to use for handling when X event happens.”
Whenever a cell changes as we described above, Excel will execute
the Worksheet_Change
event and pass in the cells that were changed through the
target object. We use this target object to take a look at which cells were
affected.
To invoke this event, go to the Sheet1
worksheet and make a change to a cell.
Then, in the VBE Immediate Window (Ctrl+G
on Windows or in Mac this window
should always be visible) you will see some text appear each time the event is
fired. Here’s an example:
Notice that whenever I change a cell, the Immediate Window shows some text based
on the cell I changed. Also note that if I change multiple values at the same
time, like cells A1:A2
, then the target object contains that specific range
and not just a single cell or array of cells.
It’s important to note that you can work with a range of cells within the same target object that is passed in to the event handler. We’ll take a look at this in the following sections.
How do I check if the Cell Changed within a Specific Range?
The target object is simply a range object that describes which cell changed (or set of cells). As shown before, you can get either a single cell, or multiple cells passed into the range (even several range areas, but that’s a topic for another post).
When you want to see if the items that changed fall within another range, you can use the following code:
Option Explicit
Public Sub Worksheet_Change(ByVal target As Range)
Dim intersection As Range
Set intersection = Intersect(target, Range("A1:B3"))
If Not intersection Is Nothing Then
Debug.Print "Cells that changed: " & target.Address(0, 0), _
vbTab & "Intersection at " & intersection.Address(0, 0)
End If
End Sub
Here is an example of how this works:
I use the Intersect()
function to determine if the target range is within
another range that I defined "A1:B3"
. The result of Intersect will be the
range of cells that intersected, if any. The Intersect()
function returns
Nothing
when no intersection is found. This is why we check for that in the If
statement.
Notice that when I select A2:C4
, the target object has the range A2:C4
, but
the intersection
range has A2:B3
. This is an important distinction to make
when working with an intersection. If you used the target
range instead of the
intersection
range, you may end up working with the wrong data (i.e. cells
C2:C4
in this case).
How to avoid infinite loops
Now that you know how to monitor changes in your worksheet and act on them,
let’s consider what would happen if you make a change to a cell within the
Worksheet_Change
event.
Consider the following code:
Option Explicit
Public Sub Worksheet_Change(ByVal target As Range)
Dim intersection As Range
Set intersection = Intersect(target, Range("A1:B3"))
If Not intersection Is Nothing Then
Dim cell As Range
For Each cell In intersection
cell.Value = "Text changed to: " & cell.Value
Next cell
End If
End Sub
In the For
loop, I go through each cell that was effected and prepend the cell
with some text.
However, changing the cell value will cause the Worksheet_Change
event to fire
again, which will take the new value of the text and again prepend it with some
more text, and on and on this will go until either Excel crashes or you close
the app.
To avoid this, we have to temporarily disable events from firing, make the change to the cell, then re-enable the events.
Option Explicit
Public Sub Worksheet_Change(ByVal target As Range)
Dim intersection As Range
Set intersection = Intersect(target, Range("A1:B3"))
If Not intersection Is Nothing Then
Dim cell As Range
Application.EnableEvents = False
For Each cell In intersection
cell.Value = "Text changed to: " & cell.Value
Next cell
Application.EnableEvents = True
End If
End Sub
Application.EnableEvents
is a boolean property that you can read / write. When
we set this to False
, any events that would normally happen (like a
Worksheet_Change
event) will be suppressed. When we turn this back to True
,
we will get the events to happen as we expect.