Microsoft Office Tutorials and References
In Depth Information
Chapter 19: Understanding Excel’s Events
You can declare the procedure by typing it manually, but a much better approach is to let the
VBE do it for you.
Figure 19-2 shows the code module for the ThisWorkbook object. To insert a procedure
declaration, select Workbook from the objects list on the left. Then select the event from the
procedures list on the right. When you do so, you get a procedure “shell” that contains the procedure
declaration line and an End Sub statement.
Figure 19-2: The best way to create an event procedure is to let the VBE do it for you.
For example, if you select Workbook from the objects list and Open from the procedures list, the
VBE inserts the following (empty) procedure:
Private Sub Workbook_Open()
Your VBA code, of course, goes between these two statements.
Event-handler procedures that use arguments
Some event-handler procedures use an argument list. For example, you may need to create an
event-handler procedure to monitor the SheetActivate event for a workbook. If you use the
technique described in the previous section, the VBE creates the following procedure:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)