home link

 

 

Synopsis

When we use the WORKSHEET_CHANGE() event handler to "make something happen when this cell or that cell changes" we need to take certain issues into consideration when writing our code.

  • Avoid creating infinite loops through accidental recursion.
  • Think about what to do if more than one of our target cells changes.
  • Be okay if rows or columns are inserted or deleted.
  • Handle things gracefully if we get passed an unexpected value.

Intercepting Cell Change Events

One of the most common requests on Excel forums is how to make something happen if a particular cell or group of cells is changed.   For example, let’s say you wanted to allow the user to input a number from 1 to 12 in cells B2 through B5 and have Excel change the number to the name of the corresponding month.

So our spreadsheet might look something like this at the beginning:

screenshot #1

The type of code solution that you will frequently see suggested is to simply hard code the addresses of the range(s) and check the address of the Target object to see if they match.   So our very first version of our code might look something like so:

' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Private Sub Worksheet_Change(ByVal Target As Range)
' _____________________________________________________________________________

If Target.Address = "$B$2" _
Or Target.Address = "$B$3" _
Or Target.Address = "$B$4" Then

Target.Formula = Format(DateValue(Target.Value & "/1/2000"), "mmmm")

End If

End Sub

see code block #1 as text file

If the user were now to enter the same values of 1, 2, and 3 in cells B2, B3 & B4 he would get:

screenshot #2

But this frequently-suggested solution has a couple of problems with it:

  1. It does not address the issue of recursive looping.
  2. It is not robust against multiple-cell entries.
  3. It is not robust against inserting or deleting rows/columns/cells
  4. It is not robust against invalid values — what if the user enters “apples” instead of a number from 1 to 12?

The first weakness is a bit hard to spot unless you know to look for it.  In older versions of Excel you could cause an infinite loop and normally one of two things would happen — you'd lock up Excel or you'd hit a stack overflow error. There are a couple of ways we can see that we're creating a loop.  One is to add a bit of code that will output a counter every time.  Something like so:

' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Private Sub Worksheet_Change(ByVal Target As Range)
' _____________________________________________________________________________

If Target.Address = "$B$2" _
Or Target.Address = "$B$3" _
Or Target.Address = "$B$4" Then

Target.Formula = Format(DateValue(Target.Value & "/1/2000"), "mmmm")

End If

Static i& ' <=== these three lines of code will let you know
Let i = i + 1 ' how many times you accidentally looped. (On the
Debug.Print i ' first run - the counter i will continue to increment,
' so you need to check the Immediate window after
' your first change to target cells

End Sub

see code block #1A as text file

If you add this code, then make a change to any one cell in B2:B4 and then look at the Immediate Window you should see a list of incrementing numbers, probably up to about 95 depending on your system and version of Excel.

A second way to see this is to set a break point somewhere in the code, then make your change and step the code, you notice you keep coming back and coming back. Loop through a few times and then on the VIEW menu in the VBE select Call Stack (or just hit your Ctrl+L key) and you'll see that you've recursively called the WORKSHEET_CHANGE event handler over and over and over.

screenshot recursion in VBE

Why does this happen? Because we're changing a cell's value inside a CHANGE EVENT HANDLER. The change that the code makes raises a second change event. The second change event means we go into the Change Event Handler a second time. The second time in we try to change the cell again and this raises a third change event and so we go into the Change Event Handler yet again and so on and so on. It's not unlike the old trick where you stand between two mirrors and you get an infinite regression of reflections.

We fix this problem by disabling events. We disable events by setting the property Application.EnableEvents to FALSE.

Note that great care must be taken to make sure you restore the Application.EnableEvents property back to TRUE.

To illustrate weakness #2 — if we select B2:B4 and type a “6” and hit Ctrl+Enter we see this …

screenshot #3

… because Target’s address was $B$2:$B$4 which is not an address we look for in our IF statement.

So we’ll fix this doesn't-handle-multiple-cells problem first. We fix this by looking for cells that intersect the cells we care about.   Then we use a For Each statement to loop through all of the cells that do intersect and do whatever we're going to do to each one of them. Our code looks something like this:

' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Private Sub Worksheet_Change(ByVal Target As Range)
' _____________________________________________________________________________

Dim celItem As Excel.Range, _
rngIntersect As Excel.Range, _
rngNumbersToMonths As Excel.Range

Set rngNumbersToMonths = Me.Range("$B$2:$B$4")
Set rngIntersect = Application.Intersect(Target, rngNumbersToMonths)

If Not rngIntersect Is Nothing Then
' ____________
Application.EnableEvents = False ' | events OFF |
' ¯¯¯¯¯¯¯¯¯¯¯¯
For Each celItem In rngIntersect.Cells
celItem.Formula = Format(DateValue(celItem.Value & "/1/2000"), "mmmm")
Next celItem
' ____________
Application.EnableEvents = True ' | events ON |
' ¯¯¯¯¯¯¯¯¯¯¯¯
End If

End Sub

see code block #2 as text file

Great! First problem fixed, now on to weaknesses #1 & #2. To illustrate, let’s take our worksheet and insert a row and a column. After doing this, our target has moved from B2:B4 to C3:C5.

screenshot #4

This results in a couple of interesting bugs.  First, if we change cell C3 to a "7", nothing happens:

screenshot #5

And secondly, if we change B3 to a "9" we get the following, where our label changes:

screenshot #6

The way to make our code robust against row/column/cell insertions or deletions is to use Excel’s tool for naming ranges.   If we name the range, then as rows or columns are inserted or deleted the named range shifts accordingly.   To do this we simply define a name and then use that name in our code. Here we’ll name our range “rgn.Write.Month.Names”.   One very important detail because it will impact the syntax we use in our VBA code is that we scope the name to the worksheet, not to the workbook.

screenshot of name dialog box

And if we select the cells on the surface of the worksheet we can see the name in the name box.

screenshot sheet's name box

We then edit our code to read as follows:

' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Private Sub Worksheet_Change(ByVal Target As Range)
' _____________________________________________________________________________

'// by using a constant at the top of the procedure we avoid embedding
'// "magic values" deeper inside the routine. This means that if the
'// name of the range changes, we simply need to update the constant
'// and we're done. We don't need to hunt through the rest of the procedure
'// to make sure we update every appearance of the range's name.

Const c_strName_WriteOutMonths As String = "rgn.Write.Month.Names"

Dim celItem As Excel.Range, _
rngIntersect As Excel.Range, _
rngNumbersToMonths As Excel.Range

Set rngNumbersToMonths = Me.Range(c_strName_WriteOutMonths)
Set rngIntersect = Application.Intersect(Target, rngNumbersToMonths)

If Not rngIntersect Is Nothing Then
' ____________
Application.EnableEvents = False ' | events OFF |
' ¯¯¯¯¯¯¯¯¯¯¯¯
For Each celItem In rngIntersect.Cells
celItem.Formula = Format(DateValue(celItem.Value & "/1/2000"), "mmmm")
Next celItem
' ____________
Application.EnableEvents = True ' | events ON |
' ¯¯¯¯¯¯¯¯¯¯¯¯
End If

End Sub

see code block #3 as text file

And if we test this, we can see that if we insert a row and a column and we enter “12” into cell C5, we get “December”.

screenshot #7

We’re getting closer now to a final solution. We have one weakness left to address and that is what should happen if a user enters an invalid value like “apples” or 256?


As is stands now, if we do this we get our old buddy – the “do you wanna debug” dialog box.

debug dialog box

Which is probably not something we want our users to see.   The code below addresses the issue of invalid values by simple rejecting them.   We even send the user a little message letting them know what we are expecting.

' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Private Sub Worksheet_Change(ByVal Target As Range)

'// version 4
'// ¯¯¯¯¯¯¯¯¯
'// this version handles errors better.
'// won't blow up if user deletes the column(s) or
'// row(s) we're trapping. It also won't fail if
'// user enters an invalid value.

Const c_strName_WriteOutMonths As String = "rgn.Write.Month.Names"

Dim celItem As Excel.Range, _
dblCellValue As Double, _
rngIntersect As Excel.Range, _
rngNumbersToMonths As Excel.Range

'// We need to account for the possibility that the user
'// may have accidentally deleted the range
'// or the name.
On Error Resume Next
Set rngNumbersToMonths = Me.Range(c_strName_WriteOutMonths)
If rngNumbersToMonths Is Nothing Then Exit Sub ' |--¿xsub?-->
Set rngIntersect = Application.Intersect(Target, rngNumbersToMonths)

If Not rngIntersect Is Nothing Then
' ____________
Application.EnableEvents = False ' | events OFF |
' ¯¯¯¯¯¯¯¯¯¯¯¯
For Each celItem In rngIntersect.Cells
'// we'll go ahead and make sure the number is a valid month
Let dblCellValue = CDbl(celItem.Value)
If dblCellValue >= 1 _
And dblCellValue <= 12 _
And Round(dblCellValue, 10) = Int(dblCellValue) Then
celItem.Formula = Format(DateValue(celItem.Value & "/1/2000"), "mmmm")
Else
If Len(celItem.Formula) > 0 Then
celItem.ClearContents
MsgBox "Please enter a whole number between" _
& vbCr & "1 and 12 in these cells.", _
vbExclamation, "Bad Month Value"
End If
End If
Next celItem
' ____________
Application.EnableEvents = True ' | events ON |
' ¯¯¯¯¯¯¯¯¯¯¯¯
End If

End Sub

see code block #4 as text file

download the Excel workbook for this article

Whew!  That seems like a long way to go just to “make something happen when we change cell B2, B3 or B4”.   But as you can see writing robust VBA means not just making it work, but trying to think of how to prevent it from breaking or recover gracefully if it something unexpected happens. If you do just a little bit of that then folks won't be nervous that your spreadsheets are going to blow up every time you tell them you've added a macro to them.


Top