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:
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.: ).
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).
In theitem , 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
Some common workbook events ():
|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|
Some common worksheet events ():
|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.
SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.
Excel ® is a registered trademark of the Microsoft Corporation.
© 2021 SuperExcelVBA | ABOUT