VBA Events

---

An Event is usually an (user-generated) action that triggers codes to be executed by VBA.

Ex: Open a spreadsheet, select a tab, change the value of cells, etc.

To create codes that will execute when an event occurs:

1. Access the VBE in the Project Explorer
2. Double-click or
3. Access the first dropdown list (Object) that will appear on the left and select an item
4. Access the second dropdown list (Procedure) that will appear on the right and select an action that will trigger the event

By default VBE Project Explorer already has the items: and . However, other items may be listed in this window, such as new Sheets or even other projects (if more than one workbook is open).

Workbook-related events must be entered in . Events related to WorkSheets must be located in the corresponding worksheet item (E.g.: ).

---

First VBA with Events

Select in the VBE Project Explorer the location you want to create a macro for an event, as previously described, and then select which event you want to use.

In this case, we will use the open event of the workbook (Open).

1. In the Project Explorer item , add to the code window the following code:

   Private Sub Workbook_Open()
   
       MsgBox "Last updated on: August 10"
   
   End Sub
   

   2. Save (in the .xlsm extension), close and open the file to receive the message.

Events are activated from specific Sub names. It is a good practice not to associate variables with these names.

By clicking on the right dropdown list, a Sub is automatically created with the name of the location and event connected to it linked with underline (_).

Default event Sub: [location]_[event].

Sub WorkBook_Open
'"WorkBook" is the location and "Open" is the event
End Sub

---

Main Workbook Events

Some common workbook events ():

Event	Description
Workbook_Open	Executes the statement when the workbook is open
Workbook_BeforeClose	Executes the statement immediately before the workbook is closed
Workbook_BeforeSave	Executes the statement immediately before the workbook is saved
Workbook_BeforePrint	Executes the statement immediately before printing
Workbook_AfterSave	Executes the statement immediately after the workbook is saved
Workbook_SheetActivate	Executes the statement every time a worksheet is activated
Workbook_SheetBeforeDoubleClick	Executes a statement every time the user double-click a cell
Workbook_SheetBeforeRightClick	Executes a statement every time the user right-click a cell
Workbook_SheetChange	Executes a statement every time the content of a cell is changed
Workbook_SheetCalculate	Executes a statement every time a value in a worksheet is calculated or recalculated
Workbook_NewSheet	Executes a statement every time a new worksheet is created
Workbook_SheetFollowHyperlink	Executes a statement every time a hyperlink is clicked

---

Main Worksheet Events

Some common worksheet events ():

Event	Description
Worksheet_SelectionChange	Executes a statement every time the selection changes
Worksheet_Activate	Executes a statement every time the worksheet becomes active
Worksheet_Deactivate	Executes a statement every time the worksheet becomes deactivated
Worksheet_BeforeDoubleClick	Executes a statement when a cell in the worksheet is double-clicked
Worksheet_BeforeRightClick	Executes a statement when a cell in the worksheet is right-clicked
Worksheet_Calculate	Executes a statement every time a value in the worksheet is calculated or recalculated
Worksheet_Change	Executes a statement every time the content of a cell in the worksheet is changed
Worksheet_FollowHyperlink	Execute a statement every time a hyperlink is clicked on the worksheet

Note that some events exist for both Workbook and Worksheet locations. The difference is only in the scope: while Workbook applies to the whole file independent of worksheet, Worksheet applies only for a specific worksheet.

---

• Previous page
• Next page

---

• report error/feedback