How to Write an Event Handler Procedure in Excel 2016 VBA
Are you curious how to write an event-handler procedure in VBA? The VBE helps you out when you’re ready to write an event-handler procedure; it displays a list of all events for the selected object.
At the top of each Code window, you find two drop-down lists:
The Object drop-down list (the one on the left)
The Procedure drop-down list (the one on the right)
By default, the Object drop-down list in the Code window displays General.
If you’re writing an event-handler for the ThisWorkbook object, you need to click ThisWorkbook in the Project window and then choose Workbook from the Object drop-down (it’s the only other choice).
If you’re writing an event-handler for a Sheet object, you need to click the specific Sheet in the Project window and then choose Worksheet from the Object drop-down list (again, the only other choice).
After you’ve made your choice from the Object drop-down list, you can choose the event from the Procedure drop-down list. Here are some of the choices for a workbook-related event.
When you select an event from the list, VBE automatically starts creating an event-handler procedure for you. This is a very useful feature, because it tells you exactly what the proper arguments are.
Here’s a little quirk. When you first select Workbook from the Object list, VBE always assumes that you want to create an event-handler procedure for the Open event and creates it for you. If you’re actually creating a Workbook_Open procedure, that’s fine. But if you’re creating a different event-procedure, you need to delete the empty Workbook_Open Sub that was created.
VBE’s help goes only so far, however. It writes the Sub statement and the End Sub statement. Writing the VBA code that goes between these two statements is your job.
You don’t really have to use those two drop-down lists, but doing so makes your job easier because the name of the event-handler procedure is critically important. If you don’t get the name exactly right, the procedure won’t work. Also, some event-handler procedures use one or more arguments in the Sub statement. There’s no way you can remember what those arguments are. For example, if you select SheetActivate from the event list for a Workbook object, VBE writes the following Sub statement:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
In this case, Sh is the argument passed to the procedure and is a variable that represents the sheet in the activated workbook.