The term “Excel event» is used to indicate certain actions performed by the user in Excel. For example, when a user switches a workbook sheet, this is an event. Entering data into a cell or saving a workbook are also Excel events.
Events can be linked to an Excel worksheet, to charts, a workbook, or directly to the Excel application itself. Programmers can create VBA code that will be executed automatically when an event occurs.
For example, to have a macro run every time the user switches a worksheet in an Excel workbook, you would create VBA code that will run every time the event occurs SheetActivate workbook.
And if you want the macro to run every time you go to a specific worksheet (for example, Sheet1), then the VBA code must be associated with the event Activate for this sheet.
The VBA code intended to handle Excel events must be placed in the appropriate worksheet or workbook object in the VBA editor window (the editor can be opened by clicking Alt + F11). For example, code that should be executed every time a certain event occurs at the worksheet level should be placed in the code window for that worksheet. This is shown in the figure:
In the Visual Basic editor, you can view the set of all Excel events available at the workbook, worksheet, or chart level. Open the code window for the selected object and select the object type from the left drop-down menu at the top of the window. The right drop-down menu at the top of the window will show the events defined for this object. The figure below shows a list of events associated with an Excel worksheet:
Click on the desired event in the right drop-down menu, and a procedure will be automatically inserted into the code window for this object Sub. at the head of the procedure Sub Excel automatically inserts the required arguments (if any). All that remains is to add the VBA code to determine what actions the procedure should perform when the desired event is detected.
Example
In the following example, each time a cell is selected B1 on the worksheet Sheet1 a message box appears.
To perform this action, we need to use the worksheet event Selection_Change, which occurs every time the selection of a cell or range of cells changes. Function Selection_Change receives as an argument Target object —. This is how we know which range of cells was selected.
Event Selection_Change occurs with any new selection. But we need the set of actions to be executed only when the cell is selected B1. To do this, we will track the event only in the specified range Target. How it is implemented in the program code shown below:
'Code to display a message box when cell B1 is selected 'on the current worksheet. Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Check if cell B1 is selected If Target.Count = 1 And Target.Row = 1 And Target.Column = 2 Then 'If cell B1 is selected, then do the following MsgBox "You have selected a cell B1" End If End Sub