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?
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
- Selecting a cell (this is another event called
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:
Want FREE Excel Tips?
Of course you do. We all do! Join our FREE email list and be first-in-line when new posts come out!
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:
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
Consider the following code:
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.
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.