Excel Macros and Worksheet Events

By Michael Alexander

Worksheet events occur when something happens to a particular Excel worksheet, such as when a worksheet is selected, a cell on the worksheet is edited, or a formula on a worksheet is calculated. Each worksheet has its own built-in module where you can place your own event procedure.

To get to this built-in module, you can right-click the worksheet and select the View Code option, as shown here.

Getting to the built-in module for a worksheet.

Getting to the built-in module for a worksheet.

Visual Basic Editor will automatically open to the built-in module for the worksheet. At the top of the module are two drop-down boxes.

In the drop-down box on the left, select the Worksheet option. The SelectionChange event in the drop-down box on the right is selected automatically. This action also adds some starter code, where you can enter or paste your code.

The default Selection Change event.

The default Selection Change event.

The idea is to choose the most appropriate event from the Event drop-down box for the task at hand. The following figure illustrates the different events you can choose.

Choose the most appropriate event.

Choose the most appropriate event.

The more commonly used worksheet events follow:

  • Worksheet_Change: Triggers when any data on the worksheet is changed

  • Worksheet_SelectionChange: Triggers each time a new cell or an object on the worksheet is selected

  • Worksheet_BeforeDoubleClick: Triggers before Excel responds to a double-click on the worksheet

  • Worksheet_BeforeRightClick: Triggers before Excel responds to a right-click on the worksheet

  • Worksheet_Activate: Triggers when the user moves from another ­worksheet to this worksheet

  • Worksheet_Deactivate: Triggers when the user moves from this ­worksheet to another worksheet

  • Worksheet_Calculate: Triggers each time a change in the worksheet causes Excel to recalculate formulas