Excel Macros and Workbook Events

By Michael Alexander

Workbook events occur when something happens to a particular Excel workbook. For example, when a workbook is opened, when a workbook is closed, when a new worksheet is added, or when a workbook is saved. Each workbook is its own built-in module where you can place your own event procedure.

To get to this built-in module, you will need to first activate the Visual Basic Editor (press Alt+F11). Then in the Project Explorer menu, right-click on ThisWorkbook, and then choose the ViewCode option.

Getting to the built-in module for a workbook.

Getting to the built-in module for a workbook.

The Visual Basic Editor will automatically open to the built-in module for the workbook. This module will have two dropdown boxes at the top.

Select the Workbook option in the dropdown on the left. This action will automatically select the Open event in the dropdown on the right. As you can see here, this will also added some starter code where you can enter or paste your code.

The default Open event for the Worksheet object.

The default Open event for the Worksheet object.

The idea is to choose the most appropriate event from the Event dropdown for the task at hand. The following figure illustrates some of the events you can choose.

Click the Event drop-down box to choose the most appropriate event.

Click the Event drop-down box to choose the most appropriate event.

The more commonly used workbook events are as follows:

  • Worksheet_Open: Triggers when the workbook is opened

  • Worksheet_BeforeSave: Triggers before the workbook is saved

  • Worksheet_BeforeClose: Triggers before Excel closes the workbook

  • Worksheet_SheetChange: Triggers when a user switches between sheets