On my website there are sample Excel files, many of which use VBA event programming to make things happen.
What is an Event?
For Excel VBA program, an event procedure is code that runs automatically, after an “event” occurs.
An event is something that happens in an Excel workbook or worksheet. For example:
- Type something in a cell (worksheet change event)
- Click on a cell to select it (worksheet selection change event)
- Click on a worksheet tab to select it (worksheet activate event)
In the Excel Visual Basic Editor, if you’re working on a Worksheet module, you can see a list of the worksheet events.
Worksheet Event Code
With event programming, it’s easy to automate your worksheets and workbooks. People don’t have to remember to click a button, or run a macro from the Macro list.
Instead, your code runs automatically when a specific event occurs. People just need to change a cell, or refresh a pivot table, and the code for that event will run.
Event Programming Example
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.
Select a Weekday
When you select a weekday, that changes the worksheet.
If the worksheet has VBA code for the Worksheet_Change event, it will run automatically, if any cell on the worksheet is changed
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 cell B2, a message is displayed — “Please update the daily roster.”
- If any other cell was changed, nothing happens
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
What happens if you try to create multiple Worksheet_Change events on the same worksheet?
For example that message code for cell B2 works well, so you’d like to add a similar Worksheet_Change event, for a different cell.
- If you make a change in cell B4, you’d like the date automatically entered in cell B5.
Visual Basic Error Message
Unfortunately, if you add another Worksheet_Change event, you’ll see an error message when you change a cell.
In the screen shot below, the VBA error message says:
- Compile error: Ambiguous name detected: Worksheet_Change
That message appears if you use the same procedure name twice on the worksheet.
Combine the Event 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
i am interested to know, in place of a drop down list box, if i am using a combo list box or active x control box, what’s the trick to monitor & trigger a macro on a change event