Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

This section provides some essential information relevant to working with events and writing event handler procedures.

Understanding event sequences

As you can see, some actions trigger multiple events. For example, when you insert a new worksheet into a workbook, this action triggers three Application -level events:

• WorkbookNewSheet : Occurs when a new worksheet is added.

• SheetDeactivate : Occurs when the active worksheet is deactivated

• SheetActivate : Occurs when the newly added worksheet is activated.

At this point, just keep in mind that events fire in a particular sequence, and knowing what the sequence is can be critical when writing event handler procedures. Later in this chapter, I describe how to determine the order of the events that occur for a particular action (see "Monitoring Application-level events").

Where to put event handler procedures

VBA newcomers often wonder why their event handler procedures aren't being executed when the corresponding event occurs. The answer is almost always because these procedures are located in the wrong place.

In the Visual Basic Editor (VBE) window, each project is listed in the Projects window. The project components are arranged in a collapsible list, as shown in Figure 19-1.

Figure 19-1: The components for each VBA project are listed in the Project window.

Each of the following components has its own code module:

• Sheet objects (for example, Sheet1, Sheet2, and so on).

• Chart objects (that is, chart sheets).

• ThisWorkbook object.

• General VBA modules: You never put event handler procedures in a general (that is, non-object) module.

• Class modules.

Even though the event handler procedure must be located in the correct module, the procedure can call other standard procedures stored in other modules. For example, the following event handler procedure, located in the module for the ThisWorkbook object, calls a procedure named WorkbookSetup , which could be stored in a regular VBA module:

Private Sub Workbook_Open() Call WorkbookSetup End Sub

Disabling events

By default, all events are enabled. To disable all events, execute the following VBA instruction:

Application.EnableEvents = False

Versions of Excel prior to Office 97 also supported events, but the programming techniques required to take advantage of those were quite different from what I describe in this chapter.

For example, if you had a procedure named Auto_Open stored in a regular VBA module, this procedure would be executed when the workbook was opened. Beginning with Excel 97, the Auto_Open procedure was supplemented by the Workbook_Open event handler procedure, which was stored in the code module for the ThisWorkbook object and was executed prior to Auto_Open .

Before Excel 97, it was often necessary to explicitly set up events. For example, if you needed to execute a procedure whenever data was entered into a cell , you would need to execute a statement such as the following:

Sheets("Sheet1").OnEntry = "ValidateEntry"

This statement instructs Excel to execute the procedure named ValidateEntry whenever data is entered into a cell. With Excel 97 and later, you simply create a procedure named Worksheet_Change and store it in the code module for the Sheet1 object.

For compatibility reasons, Excel 97 and later versions still support the older event mechanism (although they are no longer documented in the Help system). I mention old events just in case you ever encounter an old workbook that seems to have some odd statements.

To enable events, use this one:

Application.EnableEvents = True

Why would you need to disable events? One common reason is to prevent an infinite loop of cascading events.

For example, suppose that cell A1 of your worksheet must always contain a value less than or equal to 12. You can write some code that is executed whenever data is entered into a cell to validate the cell's contents. In this case, you are monitoring the Change event for a Worksheet with a procedure named Worksheet_Change . Your procedure checks the user 's entry, and, if the entry isn't less than or equal to 12, it displays a message and then clears that entry. The problem is that clearing the entry with your VBA code generates a new Change event, so your event handler procedure is executed again. This is not what you want to happen, so you need to disable events before you clear the cell, and then enable events again so that you can monitor the user's next entry.

Another way to prevent an infinite loop of cascading events is to declare a Static Boolean variable at the beginning of your event-handler procedure, such as this:

Static AbortProc As Boolean

Whenever the procedure needs to make its own changes, set the AbortProc variable to True ( otherwise , make sure that it's set to False ). Insert the following code at the top of the procedure:

If AbortProc Then AbortProc = False Exit Sub End if

The event procedure is re-entered, but the True state of AbortProc causes the procedure to end. In addition, AbortProc is reset to False .

Entering event handler code

Every event handler procedure has a predetermined name . Following are some examples of event handler procedure names :

• Worksheet_SelectionChange

• Workbook_Open

• Chart_Activate

• Class_Initialize

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

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 might 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) End Sub

This procedure uses one argument ( Sh ), which represents the sheet that was activated. In this case, Sh is declared as an Object data type rather than a Worksheet data type because the activated sheet can also be a chart sheet.

Your code can use the data passed as an argument. The following procedure is executed whenever a sheet is activated. It displays the type and name of the activated sheet by using VBA's TypeName function and accessing the Name property of the object passed in the argument:

Private Sub Workbook_SheetActivate(ByVal Sh As Object) MsgBox TypeName(Sh) & vbCrLf & Sh.Name End Sub

Figure 19-3 shows the message that appears when Sheet3 is activated.

Figure 19-3: This message box was triggered by a SheetActivate event.

Several event handler procedures use a Boolean argument named Cancel . For example, the declaration for a workbook's BeforePrint event is as follows :

Private Sub Workbook_BeforePrint(Cancel As Boolean)

The value of Cancel passed to the procedure is False . However, your code can set Cancel to True , which will cancel the printing. The following example demonstrates this:

Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim Msg As String Dim Ans As Integer Msg = "Have you loaded the 5164 label stock?" Ans = MsgBox(Msg, vbYesNo, "About to print...") If Ans = vbNo Then Cancel = True End Sub

The Workbook_BeforePrint procedure is executed before the workbook is printed. This routine displays the message box shown in Figure 19-4. If the user clicks the No button, Cancel is set to True and nothing is printed.

Figure 19-4: You can cancel the print operation by changing the Cancel argument.

Unfortunately, Excel does not provide a sheet-level BeforePrint event. Therefore, your code cannot determine which sheet is about to be printed. Often, you can assume that the ActiveSheet is the sheet that will be printed. However, there is no way to detect if the user requests that the entire workbook be printed.