� Copyright, 1998, Charles Pearson (www.cpearson.com)

 Article Summary:

This article describes the Event Model in Excel97, and how to program event procedures to automate certain tasks in your workbooks. This article assumes that you already have a working knowledge of Excel, Visual Basic For Applications (VBA), and the Excel Object Model.

When a user takes an action, Excel will look for an event procedure named Object_EventName, where Object is the object that generates and contains the event, and EventName is the name of the specific event. For example, when the user changes the selection, either by clicking on a cell in the workbook, or by using the cursor of navigation keys, Excel generates a SelectionChange event and will execute any code in a procedure named Object_SelectionChange. Similarly, changing a cell's value, either by typing a new value into the cell or by changing the .Value property from a VBA procedure, will cause Excel to trigger a Change event and execute the Object_Change event procedure.

Events for controls (e.g., the Click event for a Command Button) and events for the VBA Editor (VBE) are not discussed here.

Not all events are defined by all objects. In general, however, if an object has an event, its parent object will also have the same event. For example, the Change event is contained at the "lowest" level by the Worksheet object, and its parent (the Workbook object) also has this event, called Workbook_SheetSelectionChange. In turn, the Workbook object's parent, the Application, also has a Change event. (Application-level events work slightly differently than other events. See the Application Level Events section for more information.) You need not have the "lower" event programmed in order to receive the event at a higher level. For example, the Workbook_SheetChange event is triggered regardless of whether you have a Worksheet_Change event coded.

If you have programmed in previous versions of Excel, you are probably familiar with the Auto_Open and Auto_Close macros, which execute when the workbook is opened or closed, and with the OnEntry and OnSheetActivate properties, which execute when you enter a cell or worksheet. While these macros are still supported in Excel97, for compatibility with workbooks created in previous version, they have been replaced by event procedures.

In general, you should not use the Auto_Open and Auto_Close macros in new workbooks. While they will still work as expected, you should become familiar with and learn to use the new event procedures. Of course, if you are developing a workbook that will be used by users with an earlier version of Excel, you cannot use event procedures and must use the Auto_Open and Auto_Close macros. Remember that when you open a workbook through VBA (with the Workbooks.Open method), the Auto_Open macro is not executed. You will have to use the RunAutoMacros method of the Workbook object to run it.

Note: Chart Events apply only to Chart Sheets. They do not apply to ChartObjects embedded in a Worksheet.

Excel will pass certain arguments to Event Procedures when it calls them. Different parameters are passed to different events, but typically these are references to the Range, Worksheet, or Workbook object that caused the event to occur. For example, the Worksheet_SelectionChange event receives a Range object, called Target, which refers to the newly selected cell.

In addition, many events are passed (by reference) a Cancel parameter that can allow your code to cancel the event. For example, Workbook_BeforeSave event is passed a Cancel parameter that allows you to cancel the Save operation. Set the Cancel parameter to True to cancel the operation.

There will be an icon for each worksheet and chart sheet in your workbook, and an entry called ThisWorkbook. To add a Worksheet level event, double click on the appropriate worksheet icon to open up the code window for that worksheet. (You can also right-click on the worksheet icon, or on the worksheet tab in Excel, and choose View Code from the pop-up menu.) At the top of the code window, there are two drop down edit boxes. In the one on the left, select Worksheet and in the one on the right, select the name of the event you want to add. Excel will automatically insert the Private Sub Worksheet_event (parameters) statement and the End Sub statement.

When an event is processed by more than one object, the procedure in the "lowest level" object is executed first, and then execution proceeds "up the chain". For example, changing the value of a cell triggers the Worksheet_Change event first, then the Workbook_SheetChange event, and finally the App_WorkbookChange event.

1. Workbook_Open
2. App_WorkbookOpen
3. Workbook_WindowDeactivate (of previous workbook)
4. App_WindowDeactivate (of prevous workbook)
5. Workbook_Deactivate (of previous workbook)
6. Workbook_Activate
7. App_WorkbookActivate
8. Workbook_WindowActivate
9. App_WindowActivate
10. Auto_Open

1. Workbook_BeforeClose
2. App_WorkbookBeforeClose
3. Auto_Close
4. Excel prompts the user to save the workbook
5. Workbook_BeforeSave
6. App_WorkbookBeforeSave
7. Excel saves the workbook
8. Workbook_WindowDeactivate
9. App_WindowDeactivate
10. Workbook_Deactivate
11. App_WorkbookDeactivate

1. Workbook_WindowDeactivate
2. App_WindowDeactivate
3. Workbook_Deactivate
4. App_WorkbookDeactivate
5. Workbook_Activate
6. App_WorkbookActivate
7. Workbook_WindowActivate
8. App_WindowActivate

1. Worksheet_Deactivate
2. Workbook_SheetDeactivate
3. App_SheetDeactivate
4. Worksheet_Activate
5. Workbook_SheetActivate
6. App_SheetActivate

The Excel Application object has an EnableEvents property that you can use to enable or disable Excel's event triggers. Since VBA code, including event procedures, can cause events to be triggered, it is sometimes very important to disable events. For example, changing a cell's value from VBA will cause the Change events (in the Worksheet, Workbook, and Application objects) to trigger. If you have code in the Worksheet_Change procedure that changes another cell, you must disable events to prevent Worksheet_Change from repeatedly calling itself. This would cause Excel/VBA overflow its call stack or run out of memory.

This will declare a variable named App as an instance of the Application class. The WithEvents statement tells Excel to send Application events to this object.

At the top of the code window, there are two drop down edit boxes. In the one on the left, select App, and in the one on the right, select the event that you want to add. The VBE will automatically insert the Private Sub and End Sub statements into the module. Add the code for all the events you want to handle. Press F4 to display the Properties window, and change the name of the class module to EventClass.

This will create an object called AppClass as a new instance EventClass. Finally, you must set the App variable of the AppClass object to the actual Excel application. In the Workbook_Open procedure, use the following statement:

If Target.Address = "$A$1" Then
    Application.EnableEvents = False
    If Target.Value >= 10 Then
         Range("F10").Value = True
    Else
       Range("F10").Value = False
    End If
    Application.EnableEvents = True
End If
   

Workbook_BeforeSave

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,Cancel As Boolean)
    If Worksheets("Sheet1").Range("A1").Value < 10 Then
        Cancel = True
    End If
End Sub