Events In Excel VBA

Event programming is a very powerful tool that you can use within your VBA code to monitor user actions, take appropriate action when a user does something, or monitor the state of the application as it changes. If you are programming with your own custom classes, you can extend the functionality of these classes by defining and raising your own custom events, broadcasting the event message to any object that is listening for events from your class.

Events and Event Procedures were introduced in Excel97. Earlier versions of Excel do not support events. Events and event procedures are the same for versions 97 through 2007. No significant changes have been made to the event model since its introduction in Excel97. A few new events have been added, but the overall structure of the event system is unchanged.

An Event is an action initiated either by user action or by other VBA code. An Event Procedure is a Sub procedure that you write, according to the specification of the event, that is called automatically by Excel when an event occurs. For example, a Worksheet object has an event named Change. If you have properly programmed the event procedure for the Change event, Excel will automatically call that procedure, always named Worksheet_Change and always in the code module of the worksheet, whenever the value of any cell on the worksheet is changed by user input or by other VBA code (but not if the change in value is a result of a formula calculation). You can write code in the Worksheet_Change event procedure to take some action depending on which cell was changed or based upon the newly changed value. (The Worksheet_Change event might more properly be called Worksheet_AfterChange since it is called after the cell(s) has been changed. There is no way to access the previous value of the cell before it was changed.) For the Change event, the system will pass you a Range reference named Target that refers to the cell(s) being changed. You can examine that variable to determine if your code needs to carry out some action or whether it can ignore the change and get out with an Exit Sub statement. See the Sample Event Procedure section below.

The events and their procedure declarations are fixed. You must not alter the name or parameter list of an event procedure. The VBA Editor will automatically insert the correct procedure declaration. Do not alter this. You cannot create new events for an Excel object. The events are "hard coded" into the object, such as a Worksheet, and may not be changed. (You can, however, create custom events for your own classes. See the Creating Your Own Events In Your Class Modules section later in this article.

There are many events that are defined in a Worksheet object, the Workbook object, and the Excel Application object itself. On this page, we will cover Application events in only in general terms, but not in much detail since they require a different coding model. (See Application Events for a discussion of Application events.) An event is said to be raised when the action that initiates the event occurs and the application automatically sends a signal to all components connected to event-generating object (e.g, a Worksheet) indicating that the event has occured. An Event Procedure is a VBA Sub procedure that is executed automatically by Excel when the event is raised. It is important to remember that in addition to user input, events may run as the results of actions taken by other VBA code. There is no direct way to determine whether the cause of the event was user input or VBA code. You should write your events procedures such that this distinction does not matter. Since VBA can cause an event procedure to run, it is possible that your code may end up in a loop. See Preventing Event Loops later in this article for information about event loops and how to avoid them with proper coding.

For the Change event in particular, it should be noted that this is triggered when a cell is changed by user action or by other VBA code, but is not raised if the value of a cell is changed as a result of formula calculation.

EVENTS -- On this page, we will be discussing only Excel's events related to Sheets, Workbooks, and the Application, which are completely independent of the events for user forms and controls on user forms. The EnableEvents setting, discussed later, has no effect on events of user forms or controls on user forms. For information about supressing events for controls on a form, see Suppressing Events In UserForms. Events on Charts are a special case of events and need special code handling.

OBJECT MODULES -- Everything related to event handling -- the definition of event procedures, creating a WithEvents variable to receive events, and creating your own events -- takes place within Object Modules. Besides setting the EnableEvents property, there is nothing related to events in a standard code module; every thing is in an object module. An Object Module is any one of the following:

A Class module.
A Sheet module (either a worksheet or a chart sheet).
The ThisWorkbook module
The code module behind a User Form

You can use the Object Browser in the VBA Editor to determine what events are avaiable for the three objects that generate events -- the Worksheet, the Workbook, and the Application. (See the notes on ChartObjects and Charts in the blue box in the Event Hierarchy section below.) Open the Object Browser in the VBA Editor (press F2 or choose Object Browser from the View menu. In the Classes list, scroll down and select Worksheet. Right-click anywhere in the primary window and choose Group Members on the pop up menu. Then scroll down in the Members Of "Worksheet" list until you see items with yellow lightening bolts next to them. These are the events for the Worksheet objects. Do the same for the Workbook and Application objects. For help on a particular object or event, select it in the Object Browser and press F1 for Help on that topic (note that not all events are documented -- you may have to access event information by going through the object to which the event belongs.

Another method to determine what events are available is to create an empty class module, and enter the following code:

Dim WithEvents App As Application
Dim WithEvents WB As Workbook
Dim WithEvents WS As Worksheet
Dim WithEvents CHT as Chart

Then, select each of App, WB, WS, and CHT elements in the left side dropdown at the top of the code window. All the events for the selected item in the left dropdown will be listed in the right dropdown. If you see an item of interest, let go of the mouse button and the VBA editor will insert that event's procedure declaration in the code module. The declaration will tell you what the parameters for the event are, but you will still need to use Help to obtain a description of the meaning and usage of the parameters. Event procedures must be declared exactly as they are defined. This is why it is good practice to let the VBA Editor insert your procedure shell. Do not change any of the VBA generated code.

The easiest way to start with events and event procedures is to allow the VBA editor to build the shell code for you. In Excel, right click one of the sheet tabs at the bottom of the main window and choose View Code from the pop-up menu. This will open the VBA Editor to the code module associated with that worksheet. In that code window you will see two dropdown boxes at the top of the code window, as shown below:
EventProc1

Change the (General) setting to Worksheet and then change SeletionChange to Change. This will add the event procedure declaration for the Change event to the code module, as shown below:
EventProc2

Within the Worksheet_Change procedure, you can add any code that you want to take place when a cell value is changed. This event is raised automatically by Excel when a cell's value is changed either by user input or by other VBA code. It is not raised if the value of the cell is changed by a formula calculation in that cell. The Target parameter is a Range type object referring to the cell(s) that were changed. To use other events of the worksheet, select the event in the right hand dropdown at the top of the code pane. When you change this setting, VBA will insert the procedure shell in the code module for you, ensuring that the procedure is properly declared. Under no circumstances should you change anything in the Sub statement created by VBA. Doing so may cause the event not to work.

For sheet (both worksheet and chart sheet) level events, the event procedure code must be placed in the Sheet module associated with that sheet. Workbook level events must be placed in the ThisWorkbook code module. If an event procedure is not in the proper module, VBA will not be able to find it and the event code will not be executed. It is generally accepted good programming practice that only event procedures be included in the sheet modules and the ThisWorkbook modules. If you have no compelling reason to put other code in the sheet or ThisWorkbook modules (and there are a few quite legitimate reasons to do so, but they are beyond the scope of this article) you should put it in a standard code module.

There is no built in object to catch Application events. You can do either of two things, as described below, in the Application Events section of this page: use the ThisWorkbook code module or use a dedicated class module. Placing the code in the ThisWorkbook module requires slightly less code, but I prefer to use a dedicated class module for organizational purposes -- one module for each logical function group. Neither method is particularly better than the other. Use the method that is easiest for you.

As noted above, events are generated by:

If a Chart is a Chart Sheet, then it follows the rules of a Worksheet, except that its events are no replicated by the Workbook or Application objects. If a Chart is part of a ChartObject embedded on a worksheet, it follows its own rules. See the Charts And ChartObjects subsection, in blue, later in the article.

An object contains events for itself as well as replications of events for its subordinate objects. Since the Worksheet is at the bottom of the hierarchy and has no subordinate objects (at least no objects that have events, that have events), so the Worksheet contains only events for itself. For example, each worksheet has an event named Worksheet_Change that is triggered when a cell on that worksheet is changed either by user input or by VBA (but not if the change is the result of a calculation). Each worksheet's Worksheet_Change event is exclusive to that sheet.

SUBORDINATE AND SUPERIOR OBJECTS -- In the article, we will use the term Subordinate object to refer to an object below some other object in the hierarchy of event-generating objects. The term Superior object refers to an object that is higher up in the hierarchy of event-generating objects. For example, Worksheet is a subordinate object, to both the Workbook and Application object. The Workbook is both a subordinate and superior object; it is a superior object to the Worksheet object and is a subordinate object to the Application object. Though Charts (either Chart Sheets or Chart objects in ChartObject objects on a worksheet) do raise events, they don't fit into the hierarchy. As far as the event generation object model is concerned, Charts are orphans. See the CHARTS AND CHARTOBJECTS notes later in this section.

The Workbook object is higher up in the hierarchy. Worksheets are subordinate to the workbook. Therefore, the Workbook object has events for itself, such as BeforeSave as well has versions of all the events of the Worksheet class. For example, every worksheet has a Worksheet_Change event that is called in response to changes on that worksheet. The Workbook object also has a Workbook_SheetChange event that is called when any cell on any worksheet is changed. When a cell value is changed, both the worksheet's Worksheet_Change and the workbook's Workbook_SheetChange events are triggered, and a reference to the changed cell(s) is passed to event procedure.

Since the Application object sits at the top of the hierarchy, it contains events for itself, such as App_NewWorkbook as well as events for all Workbook events and all Worksheet events. Since every event "rolls up" to the Application object, it would be possible to write all the event code within the structure of Application Events. However, this would be very cumbersome and would not take advantage of the modularization that separates event drivers (Application, Workbook, and Worksheet) provide. The code would get very complicated very quickly.

CHARTS AND CHARTOBJECTS -- Charts do have events (although ChartObjects on a worksheet do not), but they do not fit nicely into the regular hierarchy of the event-generating objects. If you have a Chart Sheet (as oppsosed to a Chart in a ChartObject residing on a Worksheet), the chart sheet acts much the same way as a worksheet with respect to events, albeit with a different set of events. Moreover, these events do not have counterparts in the Workbook object or the Application object. Charts are kind of orphans in the grand scheme of events. A Chart object that is part of a ChartObject on a worksheet also has events, but like the Application object, there is no ready-made container for events of Charts that are part of a ChartObject on a sheet. Instead, you must use either of the techniques described later for the Application object -- just substitute "As Chart" for "As Application" and set the event class variable to Sheet1.ChartObjects("MyChart").Chart. ChartObjects do not have events -- it is the Chart object within the ChartObject object that has the events. Events for Charts, either Chart Sheets or Charts in embedded ChartObject do not have their events replicated in either the Workbook or the Application objects. Charts are sort of the "oddball" object of Excel's event system.
The following code may be placed in the ThisWorkbook object module to access events of a Chart in an embedded ChartObject object.

If you have event code in the sheet, the workbook, and the application classes, the event will be raised in all three of these objects. Even if a change is trapped by a sheet level Worksheet_Change event, the event procedure in the Workbook and the Application will also be raised. The order of events is from the least significant object (the Sheet) upwards through the most significant object (the Application). You can stop the event from being triggered "upstream" (e.g., preventing the Workbook_SheetChange and the App_SheetChange event from being raised) by setting the Application.EnableEvents property to False. For example, in a sheet's code module:

This code processes the cell change event at the Sheet level, but the line Application.EnableEvents = False prevents the Worksheet and Applicaton SheetChange events from being raised. Indeed, this line of code suppresses all events from being raised until its value is reset to True. Note that Excel never automatically sets Application.EnableEvents back to True (as it does do with the ScreenUpdating property). It is up to your code, including well designed error handling code, to ensure that Application.EnableEvents is properly reset to True. See Error Handling In VBA for more information about error handling code.

This section will examine a very simple event, the Worksheet_Change event, and illustrate a few useful techniques you can use to determine whether your code needs to act in response to the event. The basic event code, as generated by VBA is as follows:

As written, the event procedure does nothing -- there is no code within the procedure. The Target parameter is a Range object that refers to the cell(s) that were changed. Since Worksheet_Change runs for every cell in the worksheet, you most likely will need to test whether Target is within some predefined range of interest. If it is that range, you'll do something. If Target is not in that range, you don't want to carry out the action of the procedure. The easiest way to do this is with the Intersect method. Intersect returns a range of cells that are included in two ranges. For example, the Intersection of the range A1:C3 and C3:F6 is the cell C3 since that cell is common to both ranges. If there are no cells in common between two ranges, Intersect returns Nothing. Thus, you can use intersect to see if Target is within the range of interest:

You could also use named ranges rather than hard coded cell references. This is the preferred approach.

There may be times you want to act only if a single cell was changed, and ignore it if multiple cells are changed. In that case, you can use

Since Target is a Range object, you can perform any number of tests using the vast flexibility of a Range object to determine whether your code should act on the change or simply ignore it by calling Exit Sub.

It is possible that you need to respond to the events of a specific worksheet differently than you would for other worksheets, and that the name of the special worksheet is not known until run time (e.g., it might be a sheet added by your application). You could handle this in the Workbook_SheetChange event, but it would require cumbersome logic to process events for only one worksheet rather than all worksheets. A better and more elegant solution is to create a special class module and within that module declare a variable of type Worksheet using the WithEvents keyword. For example, suppose you want to handle events for a worksheet that is created at run time. In a standard code module, declare a Collection object as:

Next, create a class named CWorksheetObject and insert the following code:

This code declares a variable named WS of type Worksheet using the WithEvents keyword. WithEvents connects the event system to the WS variable and lets you utilize the events of the object. Next, you would include the event procedures for this worksheet:

Finally, you create an instance of the CWorksheetObject class and set its WS variable to the newly created worksheet. Once the WS variable is set to a specific worksheet, the event procedures in that class will run for events on the assigned worksheet.

The TestProc procedure first declares a variable named WSObj of type CWorksheetObject. At this point, the object exists, but its WS Worksheet object has not yet been set to any specific workbook, so no events will fire in the class. The code then creates a new worksheet, names that worksheet, and then sets the WSObj's WS object to the newly created worksheet. Now that the WS object has been set to a specific worksheet, it will respond to events generated by the newly created worksheet. Finally, it stores the WSObj variable in the WSColl Collection variable so the object is not destroyed when it goes out of scope at the end of the procedure.
Using the method above, and expanding on it to use other object types, you can simplify programming tasks that might otherwise require much more complicated logic.

There are two common ways to declare application events (though because VBA is as versatile as it is, there are many other ways to implement Application events). The first is to declare the App variable (of type Application in the ThisWorkbook code module. The second method is to use a dedicated code module.

In the ThisWorkbook code module, insert the following code:

Then, select App in the left side dropdown at the top of the ThisWorkbook code pane and choose in the right side dropdown which of the available events you wish to use. VBA will automatically insert the proper declarations for that event. Remember, never change the code that VBA inserts for you. If you do change it, it is quite likely that the code will not work properly, if at all.

You can then use events for the App object such as:

The second approach to creating Application Events is to use a dedicated class module. Insert a class module into your project and name the class module CExcelEvents. In that class module, enter the following code:

Then, change the left side dropdown at the top of the code pane to XLApp and choose an event from the right side dropdown. VBA will automatically insert the proper procedure shell for that event. As before, do not change the code generated by VBA.

You can then define your application event procedures in the class module. For example,

The next step is to create a variable of type CExcelEvents and initialize that variable to a new instance of CExcelEvents. In the ThisWorkbok code module, declare a variable as shown below:

Since the Class_Initialize procedure of the CExcelEvents class initializes the XLApp variable when the class is created, we do not have to worry about initializing XLApp. Any Application event procedures should be added to the CExcelEvents class.

Given that there are at least two method for creating an object to receive Application Events, you may be wondering which is better, a separate class module or the ThisWorkbook module, Neither is better in any significant way. As a matter of personal preference and coding style, I put my application events in a dedicated class module. In my opinion, this keeps to project better organized. However, beyond that, there is no advantage to use a dedicated class module for Application events. You should use the approach that seems most natural to your own coding style. Once you decide on a method, stick with that method across projects. Don't mix and match.

Without proper coding, your event procedures can end up in infinite recursive loops. Depending on your version of VBA and Excel, this may result in an non-trappable Out Of Stack Space error or VBA will simply terminate execution when some threshold (approximately 300) number of calls is met. Consider, for example, the following code:

At first glance, this code may seem perfectly valid. When a cell is changed to some value by the user, the code adds one the that value, so if a user enters 1, the code will change that to 2. However, this is not what will actually happen. When the user changes the cell to 1, the event procedure runs and changes the value to 2. This change, however, raises the Change event again and the code will run to change the 2 to a 3. This again raises the Change event, which changes the value 3 to 4. Yet again, the Change event runs, changing the 4 to a 5. This looping will continue until VBA aborts the loop or you run out of stack space.

In order to prevent this runaway looping, you can use the EnableEvents property of the Application object. When you set this property to False VBA will not raise any events, and the example Change event will run once only for the input by the user. It will not run when the value is changed by the VBA code. You should always be sure to set EnableEvents property back to True to enable events to be called normally. Unlike some properties (such as ScreenUpdating), Excel will not automatically change EnableEvents back to True. Your code must ensure that the value is properly reset. For example, in the code that follows, the Target value is incremented once, but since EnableEvents value is False, no subsequent Change event is raised.

In some circumstances, it may not be desirable to disable all event handling using Application.EnableEvents = False. Your application may rely on various events running when they should. You can work around this by creating a public Boolean variable, testing that variables in your event procedure, and exiting the procedure if that variable is True. This way, you can turn off one event handler while leaving the other event handling in place. For example, in a standard code module, declare a variable such as:

Then, in the Worksheet_Change event procedure, you test this variable. If it is true, you would immediately exit the procedure, as shown in the example below.

Finally, you would disable the Worksheet_Change event by setting the AbortChangeEvent variable to True. For example,

The code above disables only the Worksheet_Change event and only for the one line code. In general, using Application.EnableEvents = False is sufficient, but there may be circumstances in which more complex event handling is necessary.

Because the event object model includes implementations of the events of subordinate objects (e.g., Application has events for the Workbook and the Worksheet objects), you may find that some results are different than what you may expect.

EVENT ORDER: It is important to note that the event procedures of a subordinate object (e.g., the Worksheet will run to completion before an event of a superior object (e.g., Workbook) is called. That is, the Worksheet event procedure Worksheet_Change will run to conclusion, to the End Sub statement, before the Workbook event procedure Workbook_SheetChange occurs. Thus, you cannot assume that the Workbook and/or Application SheetChange have been executed within your code in the Worksheet_Change event procedure. You should assume the opposite -- the events of the superior object will not yet have run.

For example, create a class named CExcelEvents and insert the following code:

Then, put the following code in the ThisWorkbook module:

Next, put the following code in the code module for Sheet1:

Finally, put the following code in a standard code module like Module1:

Now run the procedure AAA to get things set up. Next, type something into any cell on Sheet1. Finally run the procedure BBB. Open the Immediate Window if it is not already open (CTRL G) and look at the messages. You should see:

The counter is incremented first by the event procedure Worksheet_Change in the Worksheet module, then incremented again in the Workbook_SheetChange procedure in the ThisWorkbook code module, and then incremented yet again by the App_SheetChange event procedure. Thus, the counter gets a value of 3, not 1 as you might expect. If you are using events in a superior object, you must take care that the events in the superior object do not duplicate work done in the subordinate procedure. Not properly managing events in subordinate and superior objects can cause unexpected results, such as the result of 3 in the example above.

If you are writing your own class modules (see Introduction To Classes for an introduction to working with and creating classes), you may want a class to raise an event when a specified action or condition occurs. This is a fairly simple process. Note that only object modules (class modules, userform code modules, the sheet modules, and the ThisWorkbook code module) may declare events. You cannot define, raise, or receive events in standard code modules. Also, only object modules may declare variable WithEvents and therefore only object modules may receive event triggers.

In the class that will create the events, you must first declare the events themselves with the Public Event statement, such as:

Here, EventName is the name of the event. This should be a meaningful name. Parameters is a list of any parameters that you want to pass to the class that is receiving this event, such as
X As Long, Y As Double, Z As String
This is optional. Finally, Cancel is an optional but useful parameter. It allows the class that is receiving the event to respond back to the class that contains the event that the action in question should be cancelled. For example, the Workbook_BeforeSave event has a Cancel parameter that allows you to cancel the Save operation. While a Cancel parameter is entirely optional, it is a nice touch that can add flexibility and a professional touch to your application.

Once you have declared your events, (one Public Event declaration for each event), you need raise the event at the appropriate location in your class. Where you raise the events depends entirely on the context of the executing code and what action or condition the event signifies. When I design commercial software, I use events extensively, using both a BeforeSomeAction and AfterSomeAction event pair to notify any listener that my code is about to carry out some action and to notify the listener than the action has been completed. If possible, I like to include a Cancel parameter to allow the event listener to cancel a pending operation. However, you can use events in any way you want.

You raise an event using the RaiseEvent statement. Once the event is declared, you trigger it with the RaiseEvent statement. Both declaration and raising of the event EventName are shown in the code below. Note that you cannot use Named Arguments when passing parameters to the RaiseEvent procedure.

Once you have created your class with events, you need to write the code that will receive the event triggers. Note that only object modules (class modules, a user form code module, a Sheet module, or the ThisWorkbook module -- standard code modules cannot receive events) can receive event messages. In a suitable object module, declare the event class using WithEvents:

At some point in your code, you will need to set the XLEvents variable to an instance of the CExcelEvents class, with the code:

Exactly when and where you put the object initialization code depends on what sort of module contains the event declaration. While it is technically possible to put the initialization of the variable in another procedure, this is generally a bad programming practice: it makes the code more difficult to debug and maintain. As a general rule, the code that initializes the events variable should be in the same class as the events variable. Of course, the actual event code must reside in the same object module as the events variable declaration. In a class module, the initialization would normally be in the Class_Initialize event. For a user form, the code would go in the UserForm_Initialize event.