Run Code When Cell Values Change & Other Worksheet Events

14 Sep Run Code When Cell Values Change & Other Worksheet Events

Howdee! A common requirement when developing in Excel is the need to have code run while a user interacts with a report. That is to say, the user does not need to explicitly trigger your subroutine. Therefore, today I want to cover how to run code when cell values change, as well as a handful of other worksheet events that I’ve found useful over the years. There are more worksheet events that you can use to trigger code than I’ll cover here but I think these are the most important. For a full list, click here. With that said, let’s dive in!

Run Code When Cell Values Change

I specifically mention this event because it’s the one you’ll likely find the most useful. It helps with everything from validating data entry to filtering data sets and even generating reports. To capture an event when a cell value changes, we actually subscribe to an event called “Worksheet_Change” via a private subroutine. Side note, when you have a piece of code tied to an event, it’s common to say that your subroutine is subscribing to that event. Think of it like a subscription to your favorite sports team’s news site, when something happens you expect the news service you subscribe to will alert you about it.

The easiest way to create these subscribers is to open your code editor using alt + F11 and selecting the worksheet object you want to edit by double-clicking on it in the project window on the top left. Once there, change the dropdown selection at the top that says “(General)” to “Worksheet” as pictured below. Next you can select any event you want from the dropdown on the top right, also pictured below (click on the images to view full size).

Once you select an event to subscribe to, the editor will automatically generate the necessary code snippet. It’s important that you don’t change the name of your subroutine as this is what is being triggered by the event. Most events also pass along information to their subscribers. For example, the Worksheet_Change event passes along the ByVal Target as Range variable. The ByVal keyword specifies that the underlying variable that Target represents cannot be altered by our stored procedure. So, if Target refers to cell A1, we can not use it to directly refer to cell A2 (we can however refer to it indirectly).

The Target variable refers to the cell we edit. So, if our cursor is in cell A1 and we press enter, the Target variable will refer to cell A1. This is important to remember if you’re going to be using any kind of offset to interact with the target variable.

Let’s say we want to validate that the value entered in column A was a number unless it was cell A1. This would allow the user to enter a header and a list of numbers. The code for that validation would look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Exit Sub
    Else
        If Not IsNumeric(Target) Then
            MsgBox "Value must be numeric"
            Target.ClearContents
            Target.Activate
        End If
    End If
Else
    Exit Sub
End If
End Sub

In this simple example, we simply use the IsNumeric function to verify that the number entered in the changed cell was in fact a number. The first bit of code makes sure that this code applies to all of column A except cell A1. If the user entered a value other than what we want, we alert them and then reactivate the cell for them to enter a correct value. You could change this easily to verify entries as dates, text, etc. You can set this to run when a dropdown is changed and have code conditionally trigger based on the dropdown’s value. Lots and lots of cool ways to interact with a spreadsheet as you change data on it.

Run Code When Worksheet is Activated

Having procedures kick off when a worksheet is activated can be helpful for a variety of reasons. You can refresh SQL queries, pivot tables, or other data sets whenever a worksheet is activated to ensure the user is always looking at the most recent data. For this, you subscribe to the “Worksheet_Activated” event. For this example, I’ll keep it simple for this example and we will simply refresh a pivot table when the worksheet is activated and tell the user that the pivot table was successfully refreshed. If you haven’t read my post on hooking Excel up to SQL queries, you can read it here.

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
MsgBox "PivotTable is Refreshed"
End Sub

This subroutine will refresh the pivot table each time the user clicks on the sheet where this code is implemented. An additional note here, this event can also be triggered at the workbook level. Stay tuned for an upcoming article on handling workbook level events!

Run Code to Backup Worksheet When Worksheet is Deleted

If you’re using Excel to keep track of records, you may need to ensure that you don’t ever permanently delete data. One approach of doing this is protecting the workbook to prevent any alteration to it. However, I’ve found that many users want the ability to add/delete sheets since they may do quick calculations on a spare sheet and then delete it. If I have a sheet where I need to ensure the data is intact even if the user deletes it, I use the “Worksheet_BeforeDelete” event. The code looks like this:

Private Sub Worksheet_BeforeDelete()
Dim workSheetName As String

workSheetName = ActiveSheet.Name
ActiveSheet.Name = Left(workSheetName, 30) & "@"

ActiveSheet.Copy _
After:=Sheets(ActiveSheet.Index)

ActiveSheet.Name = workSheetName
ActiveSheet.Visible = False

End Sub

This code will capture the sheet name and copy the active sheet to the same spot in the workbook and give it the same name as previous when it detects a delete worksheet event. Lastly it will hide the tab so that the user will not be aware what you’ve done. Formulas and links will be broken to the tab that was deleted but at least you’ll be able to recover any data you might need from the file after the mistake.

Run Code When User Double Clicks

This worksheet event is another one of my favorites. It can be used to make some pretty interactive templates and reports. This particular event is triggered using “Worksheet_BeforeDoubleClick”. It can be used similarly to a hyperlink to help navigate a report, help a user markup a sheet faster, or event navigate to a website. I’ll briefly cover my favorite use of this event subscriber. That is to use it as a “drill-down” action on reports. Often, users want the ability to drill down like you can in a pivot table but a pivot table may not make sense for your dashboard. Let’s assume we have a sales dashboard for a handful of regions that looks like this:

Many reviewers of this report might want to look how each store is doing in the region but providing all that context on one sheet is bulky and ugly. You could hyperlink each cell to another sheet and that would work just fine. However, I tend to avoid hyperlinks because they’re “one click actions”. The user can accidentally click the hyperlink which can get annoying. However, users rarely ever double click on a cell unless they mean to.

The first thing I do in this case is name the range of cells I want the user to interact with. In this case, that is the region names (North, South, East, West). I have chosen to name this range “navRange” in this example. No, by using the following code in a before double click worksheet event, I can effectively create a drill down of each region.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("navRange")) Is Nothing Then
    Worksheets(Target.Text).Activate
    Cancel = True
End If

End Sub

This snippet simply navigates to the worksheet that is named the same as the cell the user double clicks on. This means that the tabs need to be named the same as the cells the user is double clicking on. Lastly, the Cancel = True line is very important. This is a “BeforeDoubleClick” event which means this code was technically triggered before Excel registered the double click. That means we need to cancel this action or we will end up inside that cell trying to edit the value. The result looks like this:

I hope you found this brief introduction on how to run code when cell values change and other worksheet events useful. I plan to follow up with some more advanced stuff soon so stay tuned! If you have any questions over these or any of the worksheet events I did not cover, please drop them in the comments below. As always, you can get the file and code I went over today in the Example Files section of my site.

Cheers,

R