Writing an Event Handler Procedure

The VBE helps you out when you're ready to write an event-handler procedure; it displays a list of all events that Excel can recognize.

Figure 11-2 shows a Code window for the ThisWorkbook object (the code window is maximized to fill the entire code window area). To display this empty Code window, double-click the ThisWorkbook object in the Project window. This Code window has two drop-down lists at the top.

Figure 11-2:

An empty Code window for the This Workbook object.

Figure 11-2:

An empty Code window for the This Workbook object.

By default, the Object (left) drop-down list in the Code window displays General. To write an event-handler procedure, you need to select Workbook from the Object drop-down list. (Workbook is the only other item in the list.) If the event-handler is for a worksheet, double-click the appropriate Sheet item in the Project window before selecting Worksheet from the Object dropdown list.

Figure 11-3 shows the right drop-down list, which consists of all the workbook-related events that Excel recognizes. 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 you can verify that the proper arguments are used. (When you first selected Workbook from the Object list, VBE assumed that you wanted to create an event-handler procedure for the Open event and created it. You can see this in Figure 11-3.)

Figure 11-3:

The dropdown list displays all the workbook-related events.

Figure 11-3 shows the right drop-down list, which consists of all the workbook-related events that Excel recognizes. 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 you can verify that the proper arguments are used. (When you first selected Workbook from the Object list, VBE assumed that you wanted to create an event-handler procedure for the Open event and created it. You can see this in Figure 11-3.)

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.

Some event-handler procedures use one or more arguments in the Sub statement. 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. Examples in this chapter clarify this point.

0 0

Post a comment