VBA Workbook Events

What are VBA Workbook Events?

VBA workbook events are defined as an action performed by a user in Microsoft Excel that can trigger the execution of a specified macro. For example, when a user opens a workbook in Excel, the event “Workbook_Open” is triggered. Similarly, when the user saves the current workbook, the event “Workbook_BeforeSave” is initiated. There are many such events that are built into Excel VBA.

Users can create codes for specific workbook events, such that if the user has specified the code for a particular event that has occurred, VBA will instantly execute the code. The code that is executed when an event occurs is referred to as an event handler.

VBA workbook events allow users can create macros that are automatically executed by Excel when a particular event occurs. They improve user experience, and they make it possible to add interactivity to Excel workbooks.

Types of Events in Excel

There are different types of objects in Excel, which can have various events associated with them. Examples of Excel events include Excel itself (application), workbook, worksheet, charts, etc. The events are explained in detail below:

1. Application level events

Application events occur to the Microsoft Office application itself, such as Excel. Examples of application-level events include opening a new workbook, saving the current workbook, or closing one or more of the open workbooks.

2. Workbook level events

Workbook events occur due to the user’s actions on the workbook itself. Examples of such events include creating a new worksheet, opening a workbook, and printing the workbook.

3. Worksheet level events

Worksheet events are events that are triggered when a user performs an action on a worksheet. Examples of worksheet level events include double-clicking on a cell, right-clicking on a cell, changing a cell in the worksheet, changing the color of a worksheet, etc.

4. UserForm level events

UserForm events are events that occur to the UserForm or an object (such as a button or cell) within the UserForm. An example of a UserForm event is clicking a cell in the UserForm.

5. Chart events

Chart events are events that occur on the chart sheet. A chart sheet is different from a worksheet, and its work is to hold charts. Examples of chart events include resizing a chart and changing the selection of a chart.

WorkBook Level Events

Follow the steps below to view the list of workbook events:

• Open the VBA window from the Developer tab.
• Click on “ThisWorkbook” on the left-hand side below the Microsoft Excel Objects to open the code window.
• On the Code window, select Workbook from the drop-down option on the left. It will show the Workbook_Open code in the code window.
• Click the right-hand drop-down to see the list of workbook events.

N.B.: Clicking on any of the events will enter the code for that event on the code window.

Commonly Used VBA Workbook Events

1. Workbook Open

The Workbook Open event occurs when the workbook is accessed. It is the first message that an Excel user will see when the workbook is opened. The event can be modified to show a reminder to the user when a workbook is opened.

It can also be used in the following ways:

• Display a welcome message when the workbook is opened.
• When you want to record the time stamp every time a user opens the workbook.
• When you want to display a reminder to the next user who opens the workbook.
• When you want to display a message on a specific day of the week when the workbook is opened.

2. Workbook BeforeSave Event

The Workbook BeforeSave event is activated when an Excel user saves the current workbook, and it is triggered even before the workbook is saved. The two possible scenarios when the Workbook BeforeSave Event can be triggered include:

• When saving the workbook for the first time – In this case, it will display the Save As dialog box so that the user can specify the preferred location where the file will be saved.
• The workbook is already saved – When such an event is triggered, it will overwrite the changes in the saved version.

3. Workbook BeforeClose Event

The Workbook BeforeClose event is triggered as soon as the workbook is closed. The VBA code is executed regardless of whether the workbook is closed or not. Assuming that the user had not saved the work and gets a prompt asking to save the workbook or cancel and chooses the latter, the workbook will not be saved. However, since the BeforeClose Event has already been triggered, the event code will have already been triggered.

Other VBA Workbook Events will include the following:

• BeforePrint
• Deactivate
• NewSheet
• SheetActivate
• SheetBeforeDelete
• SheetBeforeRightClick
• WindowActivate
• WindowDeactivate
• SheetBeforeDoubleClick
• SheetBeforeRightClick

More Resources

To keep advancing your career, the additional resources below will be useful:

• Excel VBA Examples
• How to Add a VBA Button in Excel?
• How to Debug Code?
• VBA Cell References
• See all Excel resources