Excel VBA Tutorial Part 9 - Excel Events
What Are Excel Events?
The term 'Excel Events' refers to specific actions that a user carries out in Excel. For example, if the user selects a Worksheet, this is an event. Similarly, entering data into a cell or saving a Workbook are also Excel events.
Events are linked to Excel Worksheets, Charts, Workbooks, or to the Excel Application itself. This enables the programmer to create vba code that is executed automatically at the time of an event.
For example, if you wanted to run a macro every time a user selects any Worksheet in the Workbook, this could be done by writing vba code that is linked to the Workbook event "SheetActivate".
Alternatively, if you wanted to run a macro every time a specific Worksheet (e.g. "Sheet1") is selected, you would link your code to the Worksheet event "Activate" for Sheet1.
How to Link Code to a Specific Event
-
Open up the VBA Editor (by pressing the keyboard combination Alt + F11 ) and open up the code window for the object that you want to link code to. For example, if you want to execute a section of code every time a specific worksheet event occurs, you should open up the code window for that worksheet.
This is shown in the image below:
-
Click on the drop-down menu, at the top left of the code window and select the object type (e.g. Worksheet). The dropdown menu on the right will then display the events that are defined for that object.
The image below shows the events that are defined for an Excel Worksheet:
-
Select an event from the top right drop-down menu. This causes a Sub procedure to be automatically inserted into the code window. The arguments that Excel automatically supplies to that function (if there are any) are included into the Sub header, so you just need to add the vba code to define what you want to do when that event occurs.
Example
The following example displays a message box every time the cell B1 in the Worksheet "Sheet1" is selected.
For this action, we need to use the Worksheet Event "SelectionChange", which occurs every time a different cell or range of cells is selected. The "Worksheet_SelectionChange" Sub procedure receives, as an argument, a Range object called "Target". This stores the range of cells that has been selected.
For this example, we only want to display a message box when cell B1 has been selected. However, the "SelectionChange" event relates to any new selection. Therefore, for this example, we need to check the supplied Range, 'Target', as soon as the function is called, and only display the message box if 'Target' is equal to cell B1. The code for this is shown below:
|
' Code to display a Message Box if Cell B1 of the current Worksheet
' is selected. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Check if the selection is cell B1
End Sub
If Target.Count = 1 And Target.Row = 1 And Target.Column = 2 Then
' The selection IS cell B1, so carry out required actions
End If
MsgBox "You have selected cell B1" |