Choose your language

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 Thisworkbook or sheet1
  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
Creating Event

By default VBE Project Explorer already has the items: Thisworkbook and sheet1. 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 Thisworkbook. Events related to WorkSheets must be located in the corresponding worksheet item (E.g.: sheet1).


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).

Event DropDown
  1. In the Project Explorer item Thisworkbook, 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.

Event Example

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 (_).

Change Event VBA

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 (Thisworkbook):

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 (sheet1):

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.



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.

© 2019 SuperExcelVBA | ABOUT

Protected by Copyscape