How To Trigger Your VBA Macros To Run Based On A Specific Cell Value Change
Today we are going to discuss how you can automatically make your VBA code execute based on a specific cell value being changed. Behind the scenes, Excel keeps track of specific events that occur while the user is working on their spreadsheet. These tracked events are called Event Handlers and we can use Event Handlers as triggers to kick off our macros based on what our users are doing to their spreadsheets.
What Are Some Examples of Event Handlers?
There are a bunch of Event Handlers available to you and they fall into two categories: Workbook and Worksheet Events. What the handlers capture is fairly evident by their names, so I won’t go into detail on what each on does. But I will go ahead and list some of them out so you can have an idea of what is available to you.
Workbook Events
- Open
- SheetChange
- SheetActivate
- BeforeClose
- BeforeSave
- NewChart
Worksheet Events
- Change
- Activate
- BeforeDelete
- BeforeDoubleClick
- BeforeRightClick
- SelectionChange
Where Are Event Handlers Located?
Event Handlers are not stored in your typical module location. They are actually stored inside either your Workbook or Worksheet object. To get to the “coding area” of either your workbook or worksheet, you simply double-click ThisWorkbook or the sheet name (respectively) within your desired VBA Project hierarchy tree (within the Project Window of your Visual Basic Editor).
How Do I Add An Event Handler?
Event Handlers have very specific subroutine names and variables. It is very important not to have any typos within the sub name or the variables it declares. To guarantee avoidance of any typos, I always recommend having the Visual Basic Editor set up the Event Handler code for you. To do this, select either Worksheet or Workbook from the Object drop-down box and then select the Event Handler you wish to use from the Procedure drop-down box.
Let’s Walk Through An Example!
Hopefully, you now have some sort of conceptual understanding of Event Handlers, but how do you use them in the real world? Let’s walk through a very simple example that will allow us to run some VBA code whenever the Cell G7 contains the word “yes” on our spreadsheet.
Below is going to be our interface where Excel will reveal a joke if you type the word “Yes” into the blank box (Cell G7).
The Example in Action
You can see through the following diagram, immediately after I type in “Yes, tell me a joke!”, a super-funny joke magically appears!
The VBA Behind The Spreadsheet
To get this spreadsheet working as intended, I needed to add an Event handler, to capture anytime there was a change to the cell G7. I choose to use the Change Event Handler to make this happen.
What the Change event captures is any change made to your spreadsheet (excluding formatting changes). You’ll notice that the Worksheet_Change subroutine has a variable named Target that gets fed into it. This variable is a Range-type and will be the cell that was last changed by the user. So if I changed the value in Cell A1, the Worksheet_Change subroutine would kick off and pass Cell A1 into the Target variable.
With this in mind, we are going to want to test the cell that was changed to see if it matches the cell we are concerned with. We can do this by starting our code off with a very simple IF Statement that acts as a door to the rest of the code. The IF statement checks to see if the cell(s) that was last changed intersects (or matches) cell G7.
'Determine if change was made to cell G7
If Not Intersect(Target, Range("G7")) Is Nothing ThenIf the Target cell does not have access to get past the door, then the code ends and it will happen so fast, that your user won’t know anything has happened.
Below is the code in full that will test if the word “Yes” was entered into cell G7 anytime a change happens within Sheet1:
'Remove Case Sensitivity
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made to cell G7
If Not Intersect(Target, Range("G7")) Is Nothing Then
'Determine if the work "yes" is contained within cell G7
If InStr(1, Range("G7"), "Yes") > 0 Then
Range("G9").Font.Color = Range("F5").Font.Color
Else
Range("G9").Font.Color = Range("G9").Interior.Color
End If
End If
End SubAnd here is a diagram of where you would place the code within your VBA Project:
Why Aren’t My Events Getting Captured?
Many people will turn off the tracking of Event to speed up their VBA code. If you are relying on capturing events within your spreadsheet and they don’t seem to be working, you can run the following command in the Immediate Window (use the shortcut Ctrl + g to make this window visible) within the Visual Basic Editor.
Just hit your enter key after typing the below phrase into the Immediate Window:
Application.EnableEvents = TrueGet The Example File Used In This Article
Sometimes seeing things in action may help you learn much better than reading about it in an article. That is why I am making the example I walked through in this article available to you. The workbook and its code are completely unlocked so you can dig in and discover how all the magic works.
If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.