Excel 2019 Bible

Part VI: Automating Excel

Entering Event-Handler VBA Code

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

book-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

procedure by typing it, but a much better approach is to let the Visual Basic Editor (VBE) do

it for you by using the two drop-down controls at the top of the window.

Figure 46.1 shows the code module for the ThisWorkbook object. Select this code mod-

ule 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 and then select the

event from the procedures list in the upper right. When you do, you get a procedure “shell”

that contains the procedure declaration line and an End Sub statement.

FIGURE 46.1

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()

End Sub

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

Some event-handler procedures contain an argument list. For example, you may need to

create an event-handler procedure to monitor the SheetActivate event for a workbook.