Excel VBA Events, Event Procedures (Handlers), Triggering a VBA Macro
Related Links:
Worksheet event – Change Event – is illustrated in chapter:
Worksheet Change Event in VBA and Preventing Event Loops;
Worksheet event – Selection Change Event – is illustrated in chapter:
Worksheet Selection Change Event, Excel VBA;
Event Procedures of ActiveX Controls – Click event of CheckBox / ListBox / ComboBox / ToggleButton, Change event of the ScrollBar, SpinDown & SpinUp events of SpinButton, the GotFocus event of the TextBox – are illustrated in chapter:
ActiveX Controls, Form Controls & AutoShapes on a Worksheet;
————————————————————————
Contents:
Event procedures must reside in their appropriate location
Excel events can be broadly classified into six types
————————————————————————
Events are actions performed, or occurences, which trigger a VBA macro.
A VBA code is triggered when an event occurs such as, clicking on a button (which can be in a form or a worksheet), opening / closing / saving / activating / deactivating the workbook, selecting a cell or changing cell selection in a worksheet, making a change in the content of a worksheet cell, selecting or activating a worksheet, right-clicking, when a worksheet is calculated, at periodic intervals or at a specific day or time, or pressing on a particular key(s).
An event procedure (also referred as event handler) is a procedure with a standard name that runs on the occurrence of a corresponding event.
The event procedure contains the user written customized code which gets executed when the specific Excel built-in event, such as worksheet change event, is triggerred. Event Procedures are triggered by a predefined built-in event and are installed within Excel having a standard & predetermined name viz. like the Worksheet Change event procedure is installed with the worksheet – “Private Sub Worksheet_Change(ByVal Target As Range)”. Event procedures are attached to objects like Workbook, Worksheet, Charts, Application, UserForms or Controls. An Event Procedure is automatically invoked when an object recognizes the occurrence of an event. In the Worksheet Change event procedure, the Worksheet object is associated with the Change event (ie. with the worksheet change event, a sub-procedure containing customized code runs automatically when you change the contents of a worksheet cell). An event procedure for an object is a combination of the object’s name (as specified in the Name property), an underscore and the event name.
To use the Excel provided event procedures, in the Visual Basic Editor select an object from the Object box and then select a corresponding procedure from the Procedure box. After selecting the specific event you will get a procedure “shell” in the code window (as shown below), where you insert the vba code you want to be executed:
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
Another example is for a (UserForm) check box control which has the Click event: where the name of the check box is CheckBox1, the event handler procedure will be named as follows:
Private Sub CheckBox1_Click()
End Sub
Event procedures must reside in their appropriate location:
Event Procedures: The objects for which you have built-in events are Workbook, Worksheets and Chart Sheets, Userform and its Controls. To use the Excel provided built-in event procedures, in the Visual Basic Editor select an object from the Object box and then select a corresponding procedure from the Procedure box. After selecting the specific event, insert the vba code you want to be executed. Event procedures reside in their appropriate object modules or Excel will not be able to find and execute them – ThisWorkbook module (for Workbook events), Sheet modules (worksheets and chart sheets events) and UserForm modules (for events related to UserForm & its Controls). General vba code in respect of events not associated with a particular object such as workbook or worksheet, are placed in the standard code module.
Custom Events: You can also define your own events in custom classes (class modules), and create event procedures that run when those events occur. Though Application Events can be created in any object module, they should logically be placed in either an existing object module like ThisWorkbook or you can create a class module to handle them. Chart sheet events (ie. if a chart is a chart sheet) reside within its chart sheet and are enabled by default, similar to as worksheet events reside in their worksheet. But for using events for a chart object representing an embedded chart in a worksheet, a new class module has to be created.
Excel events can be broadly classified into six types, as follows:
1. Workbook events are actions associated with the workbook, to trigger a VBA code or macro. Opening / closing / saving / activating / deactivating the workbook are examples of workbook events viz. with a workbook open event, you can run a sub-procedure automatically when a workbook is opened. ThisWorkbook is the name of the module for the workbook and is used to place workbook events and Application Events. Workbook events code must be placed in the code module for the ThisWorkbook object, and if they are placed in standard code modules, Excel will not be able to find and execute them. To create a workbook event: use the Visual Basic Editor -> in the Project Explorer, double click on “ThisWorkbook” (in the Microsoft Excel Objects folder in your VBAProject) -> in the Code window, select Workbook in the Object drop-down list on the left and then select the relevant event from the Procedure drop-down list on the right. After selecting the specific event, insert the vba code you want to be executed. If you select the Open event, the code window will show:
Private Sub Workbook_Open()
End Sub
Refer Image 1 – Workbook events in Code window.
2. Worksheet events are actions or occurrences associated with the worksheet, to trigger a VBA code or macro. Instances of worksheet events are: selecting a cell or changing cell selection in a worksheet, making a change in the content of a worksheet cell, selecting or activating a worksheet, when a worksheet is calculated, and so on. For example, with the worksheet change event, a sub-procedure runs automatically when you change the contents of a worksheet cell. Worksheet Event procedures are installed with the worksheet, ie. the code must be placed in the code module of the appropriate Sheet object and if they are placed in standard code modules, Excel will not be able to find and execute them. A sheet module has the same name as the worksheet’s code name with which it is associated viz. Sheet1, Sheet2, … In VBE, the code name of the selected worksheet appears to the right of (Name) in the Properties Window while the sheet name appears to the right of Name when you scroll down in the Properties Window. The code name can be changed only in the Properties window and not programmatically with code. Sheet module can be for a worksheet or a chart sheet and is used to place worksheet and chart sheet events. To create a worksheet event: use the Visual Basic Editor -> in the Project Explorer, double click on the appropriate sheet (in the Microsoft Excel Objects folder in your VBAProject) -> in the Code window, select Worksheet in the Object drop-down list on the left and then select the relevant event from the Procedure drop-down list on the right. After selecting the specific event, insert the vba code you want to be executed. If you select the Change event, the code window will show:
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
Refer Image 2 – Worksheet events in Code window.
3. Chart events are events for a chart sheet or an embedded chart, examples include Activate, Select or SeriesChange. A Sheet module can be for a worksheet or a chart sheet and is used to place worksheet and chart sheet events. Chart sheet events (ie. if a chart is a chart sheet) reside within its chart sheet and are enabled by default, similar to as worksheet events reside in their worksheet. But for using events for a chart object representing an embedded chart in a worksheet, a new class module has to be created.
Refer Image 3 – Chart events (ie. chart sheet) in Code window.
4. UserForm events: UserForm events are pre-determined events that occur for a particular UserForm and its Controls, examples include Initialize, Activate or Click. To display a UserForm, you use the Initialize event and run the event procedure – Private Sub UserForm_Initialize() – code is inserted in the Initialize event procedure to initialize variables or UserForm objects and set their properties before the UserForm is displayed. With the Click event of the CommandButton of a UserForm, clicking on the CommandButton will run the event procedure – Private Sub CommandButton1_Click() – which contains your customized code. Event procedures for UserForm or its Controls are placed in the Code module of the appropriate UserForm. You must double-click the body of a UserForm to view the UserForm Code module, referred to as a module ‘behind’ the UserForm, and then select UserForm or its Control from the Object box and then select a corresponding procedure from the Procedure box. After selecting the specific event, insert the vba code you want to be executed. Remember to set the Name property of controls before you start using event procedures for them, else you will need to change the procedure name corresponding to the control name given later. Only event procedures for the UserForm or its controls should be placed in the user form code module.
Refer Image 4a – UserForm events in Code window.
Refer Image 4b – CommandButton (UserForm Control) events in Code window.
5. Application events are events that occur for a particular application (ex, Excel itself); these are like workbook or worksheet events, except that they do not have their own specific built in object module. Application events occur when a Workbook or PvotTable is opened or created, or when any Worksheet changes. Examples are: WorkbookOpen (ie. a workbook is opened), NewWorkbook (ie. a new workbook is created), SheetActivate, SheetCalculate, SheetChange, SheetSelectionChange, SheetPivotTableUpdate, and so on. Application level events are not available by default. The Application event can be created in a Dedicated Class Module or in an existing object module like ThisWorkbook.
6. Events not associated with objects, such as OnTime method (automatically trigger a vba code at periodic intervals or at a specific day or time) and OnKey method (run a specific code on pressing a key or combination of keys). Because these are not associated with a particular object like workbook or worksheet, their codes rest in the standard code module.