This page has been replaced. If you are not redirected,
Click Here.

Event Procedures
In Microsoft
Excel97


This article applies only to Excel97 (version 8.0) and later versions. It does not apply to previous versions of Excel. The phrase "Excel" should be taken to mean "Excel97 and above".

This article was written by Chip Pearson, 10-Oct-1998.

� 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.

Article Contents:

 


What Are Events?

In Excel97, the Application will trigger certain events when a user (or VBA macro) takes some action. As a user or a programmer, you do not call or trigger event procedures � they are automatically called by the Excel Application.

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.

 

Objects That Contain Events

There are four objects in Excel that can contain events.

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.

 

Event Procedures And Auto_ Macros And On� Macros

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.

 

List And Description Of Events

The following tables list the events for the four objects listed in the previous section. Events for controls are not listed.

Worksheet Events

Event Name

Description

Worksheet_Activate

This event occurs when a worksheet is activated (displayed).

Worksheet_BeforeDoubleClick

This event occurs when the user double-clicks on a worksheet cell. This event provides a Cancel parameter.

Worksheet_BeforeRightClick

This event occurs when the user right-clicks on a worksheet cell. This event provides a Cancel parameter.

Worksheet_Calculate

This event occurs then a cell on the worksheet is calculated.

Worksheet_Change

This event occurs when the value of a cell is changed. This event does not occur when the value is changed as the result of a calculation.

Worksheet_Deactivate

This event occurs when a worksheet is deactivated (another worksheet is displayed).

Worksheet_SelectionChange

This event occurs when the active selection is moved to a new range.

 

Workbook Events

Event Name

Description

Workbook_Activate

This event occurs when the workbook is activated. It does not occur when you switch applications.

Workbook_AddInInstall

This event occurs when an Add-In module is installed (enabled).

Workbook_AddInUninstall

This event occurs when an Add-In module is uninstalled (disabled).

Workbook_BeforeClose

This event occurs when the workbook is closed. This event provides a Cancel parameter.

Workbook_BeforePrint

This event occurs when the workbook is printed. This event provides a Cancel parameter.

Workbook_BeforeSave

This event occurs when the workbook is saved. This event provides a Cancel parameter.

Workbook_Deactivate

This event occurs when the workbook is deactivated. It does not occur when you switch applications.

Workbook_NewSheet

This event occurs when a new worksheet is added to the workbook.

Workbook_Open

This event occurs when the workbook is opened.

Workbook_SheetActivate

This event is the workbook implementation of the Worksheet_Activate event.

Workbook_SheetBeforeDoubleClick

This event is the workbook implementation of the Worksheet_BeforeDoubleClick event. This event provides a Cancel parameter.

Workbook_SheetBeforeRightClick

This event is the workbook implementation of the Worksheet_BeforeRightClick event. This event provides a Cancel parameter.

Workbook_SheetCalculate

This event is the workbook implementation of the Worksheet_Calculate event.

Workbook_SheetChange

This event is the workbook implementation of the Worksheet_Change event.

Workbook_SheetDeactivate

This event is the workbook implementation of the Worksheet_Deactivate event.

Workbook_SheetSelectionChange

This event is the workbook implementation of the Worksheet_SelectioChange event.

Workbook_WindowActivate

This event occurs when a workbook window is activated.

Workbook_WindowDeactivate

This event occurs when a workbook window is deactivated.

Workbook_WindowResize

This event occurs when a workbook window is resized.

 

Chart Events

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

Event Name

Description

Chart_Activate

This event occurs when the chart sheet is activated.

Chart_BeforeDoubleClick

This event occurs when the user double-clicks on the chart sheet. This event provides a Cancel parameter.

Chart_BeforeRightClick

This event occurs when the user right-clicks on the chart sheet. This event provides a Cancel parameter.

Chart_Calculate

This event occurs when the chart is calculated or an element of the chart is changed.

Chart_Deactivate

This event occurs when the chart sheet is deactivated.

Chart_DragOver

This event occurs when the user drags data over the chart sheet.

Chart_DragPlot

This event occurs when the user drags a range of cells over the chart cheet.

Chart_MouseDown

This event occurs when the user presses a mouse button while over the chart.

Chart_MouseMove

This event occurs when the user moves the mouse over the chart.

Chart_MouseUp

This event occurs when the user releases the mouse button.

Chart_Resize

This event occurs when the chart is resized.

Chart_Select

This event occurs when the user selects the chart.

Chart_SeriesChange

This event occurs when the value of the value of a datapoint is changed.

 

Application Events

Event Name

Description

App_NewWorkbook

This event occurs when a new workbook is added.

App_SheetActivate

This event is the Application implementation of the Worksheet_Activate event.

App_SheetBeforeDoubleClick

This event is the Application implementation of the Worksheet_BeforeDoubleClick event. This event provides a Cancel parameter.

App_SheetBeforeRightClick

This event is the Application implementation of the Worksheet_BeforeRightClick event. This event provides a Cancel parameter.

App_SheetCalculate

This event is the Application implementation of the Worksheet_Calculate event.

App_SheetChange

This event is the Application implementation of the Worksheet_Change event.

App_SheetDeactivate

This event is the Application implementation of the Worksheet_Deactivate event.

App_SheetSelectionChange

This event is the Application implementation of the Worksheet_SelectionChange event.

App_WindowActivate

This event is the Application implementation of the Workbook_WindowActivate event.

App_WindowDeactivate

This event is the Application implementation of the Workbook_WindowDeactivate event.

App_WindowResize

This event is the Application implementation of the Workbook_WindowResize event.

App_WorkbookActivate

This event is the Application implementation of the Workbook_Activate event.

App_WorkbookAddInInstall

This event is the Application implementation of the Workbook_AddInInstall event.

App_WorkbookAddInUninstall

This event is the Application implementation of the Workbook_AddInUnInstall event.

App_WorkbookBeforeClose

This event is the Application implementation of the Workbook_BeforeClose event. This event provides a Cancel parameter.

App_WorkbookBeforePrint

This event is the Application implementation of the Workbook_BeforePrint event. This event provides a Cancel parameter.

App_WorkbookBeforeSave

This event is the Application implementation of the Workbook_BeforeSave event. This event provides a Cancel parameter.

App_WorkbookDeactivate

This event is the Application implementation of the Workbook_Deactivate event.

App_WorkbookNewSheet

This event is the Application implementation of the Workbook_NewSheet event.

App_WorkbookOpen

This event is the Application implementation of the Workbook_Open event.

 

Parameters To Event Procedures

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.

When you program an event in the VBA Editor (VBE), the VBE will automatically insert the required parameter declarations. For more information, see the specific event in the on-line VBA/Excel help files for a description of each parameter.

You cannot add additional parameters to event procedures.

 

Programming Event Procedures In VBA

Since event procedures are stored "behind" the objects that contain them, you do not include them in standard Code Modules. Instead, you create them in the modules that contain them. If you create an event procedure in a standard Code Module, Excel will not recognize it as a valid event procedure, and it will never be executed.

In the VBE, open the Project Explorer window from the View menu (or just press Ctrl+R). In this window, you will see a "tree-view" listing all of the open workbooks. Locate your workbook in the list, and expand that branch. You'll see a "folder" called Excel Objects. Expand this branch of the tree.

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.

If you want to add a Workbook level event, open the code module for the ThisWorkbook item, and follow the procedures listed above. The only code that should be in these modules is event procedures, and declarations for the modules. Do not put your own macros and functions in these modules.

Application level events require a special Class Module. See the Application Level Events section later for more details.

 

Order Of Event Procedures

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.

When opening a workbook, the order of events is as follows:

  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

 

When closing a workbook, the order of events is as follows:

  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

 

When switching between two open workbooks, the order of events is as follows:

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

 

When switching between two worksheets within a workbook, the order of events is as follows:

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

 

My EventSeq.xls workbook (available on my Downloads page at www.cpearson.com ) will display a message box for every event for every object in the workbook, along with the values of the parameters to each procedure. You can use this to test the order of events for various operations.

 

Enabling And Disabling Events

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.

You can disable events with

Application.EnableEvents = False

and re-enable them with

Application.EnableEvents = True

Remember that this setting is for the entire Excel application, so setting it to False will affect all of your open workbooks, not just the workbook containing the code. Also, remember that Excel does not restore the setting when your code ends, so be sure to set it back to True if you do set it to False.

Note: Application.EnableEvents applies only to Excel objects (Sheets, Workbook, and the Application). EnableEvents has no effect whatsoever on events of control on a UserForm. These events will trigger regardless of the value of Application.EnableEvents. See the Events In Userform Controls page for information about events of userform controls.

Application Level Events

Worksheet and Workbook level events are contained by the Worksheet and Workbook objects, respectively. However, there is no similar object to contain the Application level events. Therefore you must use a Class Module to create a object that can accept and handle Application level events.

First, open your workbook in the VBA Editor, and choose Class Module from the Insert menu to create a new Class Module your workbook. Open the class module, and insert the following statement as a global declaration:

Public WithEvents App As Application

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.

Next, open a new or existing standard code module, and insert the following statement as a global declaration:

Public AppClass As New 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:

Set AppClass.App = Application

You must save and close the workbook, and then reopen it, in order to enable the application event handlers. When this workbook is open, it will receive application level event messages from all open workbooks. This way, you can have "global" control over all open workbooks in an application.

The AppEvent.xls workbook on my Downloads page illustrates the use of a class module to catch Application level events.

 

Sample Event Procedures

This section lists and describes some sample Event Procedure code.

Worksheet_Change

This procedure will change the value of F10 to TRUE if the value in A1 is changed to a value greater than or equal to 10. Note that we change Application.EnableEvents to False in order to prevent the Change event from calling itself, and then restore the setting back to True.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

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

This procedure will prevent the user from saving the worksheet if the value in A1 is less than 10.

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
   

For More Information

For more information about the Event Procedures in Excel97, see the on-line help files, or see my Excel site on the Internet: www.cpearson.com  .

This article may be freely distributed in any form, as long as it is distrubuted in its entirety, without modification, and contains this paragraph. � Copyright, 1998, Charles Pearson. Microsoft, Windows, Windows95, VBA, Visual Basic For Applications, Excel, and Excel97, may be trademarks or registered trademarks of Microsoft Corporation.