|
This page has been replaced. If you are not redirected, Click Here. Event Procedures 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
Workbook Events
Chart Events Note: Chart Events apply only to Chart Sheets. They do not apply to ChartObjects embedded in a Worksheet.
Application Events
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:
When closing a workbook, the order of events is as follows:
When switching between two open workbooks, the order of events is as follows:
When switching between two worksheets within a workbook, the order of events is as follows:
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 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) 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. |
|
This site created By Chip Pearson
At Pearson Software Consulting., LLC |