On my website there are sample Excel files, many of which use event programming to make things happen. Typing something in a cell, selecting a different cell, activating a worksheet, and many other actions, are Events. Add a bit of code in the background, and things can happen automatically if an event occurs. No need to click a button to run a macro, just change a cell, or refresh a pivot table, and the code for that event will run.
For example, on this worksheet there’s a data validation drop down list in cell B2, where a day of the week can be selected.
When you select a weekday, that changes the worksheet, and could trigger a Worksheet_Change event.
In this example, the Worksheet_Change event code checks the address of the cell that was changed, to see if it was cell B2. If it was, a message is displayed.
View the Worksheet Code
To see the code for a worksheet, right-click on its sheet tab, and click View Code.
The Visual Basic Editor opens, and shows the code for the active sheet.
Multiple Worksheet Events
That code works so well, that you’d like to add another Worksheet_Change event. If you make a change in cell B4, you’d like the date automatically entered in cell B5. Unfortunately, if you add another Worksheet_Change event, you’ll see an error message when you change a cell, because you've used the same procedure name twice on the worksheet.
Combine the Code
Instead of creating separate events with the same name, combine both pieces of code into one. For example, you could use Select Case, and specify what should happen when specific cells are changed.
Some situations will require a more complex solution, and if you experiment a bit, you should be able to include multiple outcomes within a single worksheet event’s code.
______________________
A warning though for those enticed by the above; monitoring multiple ranges through the worksheet_change event can lead to drastically poorer performance if your sheet is large and/or calculation intensive!
A bit misleading here. The Ambiguous Name compile error is because you have created 2 subs by the same name, NOT because you are trying to catch more than one change event. The same compile error will occur if you create 2 subs with the name Private Sub Me(), thus a NAME compile error and Not the event.
You can add as many worksheet_change events as you want to, but as Gordon above says it slows down the sheets performance so use scarcely. The most common mistake with the worksheet_change event is a continuous loop. This can be prevented by setting the event handler to false in the beginning and if target.address is found, do whatever and opt out of target code, set Event Handler to True so change can be made and Exit Sub
See sample code below
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Address = Range("H171").Address Then
If Not Target = UCase(Target) Then
Target = UCase(Target)
End If
Range("K171").Value = vbNullString
GoTo EventHandler
End If
If Target.Address = Range("K171").Address Then
If Not Target = UCase(Target) Then
Target = UCase(Target)
End If
Range("H171").Value = vbNullString
GoTo EventHandler
End If
EventHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
Incidentally the part code is copied from a worksheet as a fillable form monitoring 64 change_events without noticing performance slowdown- no calculations
Thanks Gordon and Kobus -- I appreciate your comments and thanks for the sample code.
Sometimes, you need to know whether the changed cell is within a range of cells. Here, the Intersect method comes in handy:
Private Sub Worksheet_Change(ByVal Target as Range)
On Error Resume Next
If Not Application.Intersect(Target, ActiveSheet.Range("TestRange")) Is Nothing Then
'do some test here
End If
End Sub