Excel 2010 Bible

Part VI: Programming Excel with VBA

874

Entering Event-Handler VBA Code

Every event-handler procedure must reside in a specific type of code module. Code for workbook-

level events is stored in the ThisWorkbook code module. Code for worksheet-level events is

stored in the code module for the particular sheet (for example, the code module named Sheet1).

In addition, every event-handler procedure has a predetermined name. You can declare the proce-

dure by typing it, but a much better approach is to let the VB Editor do it for you, by using the two

drop-down controls at the top of the window.

Figure 43.1 shows the code module for the ThisWorkbook object. Select this code module by

double-clicking it in the Project window. To insert a procedure declaration, select Workbook from

the objects list in the upper left of the code window. Then select the event from the procedures list

in the upper right. When you do, you get a procedure “shell” that contains the procedure declara-

tion line and an End Sub statement.

FIGURE 43.1

The best way to create an event procedure is to let the VB Editor do it for you.

For example, if you select Workbook from the objects list and Open from the procedures list, the

VB Editor inserts the following (empty) procedure:

Private Sub Workbook_Open()

End Sub

Your event-handler VBA code goes between these two lines.