Running a Macro When a Worksheet is Activated

Excel allows you to define macros that are executed whenever certain well-defined events occur in the Excel environment. One of those events is when a worksheet is activated. For instance, you could create a macro that defined a custom menu structure whenever a particular worksheet is displayed.

In order to create a macro that runs when a worksheet is activated, follow these steps:

1. Display the VBA Editor by pressing Alt+F11.
2. In the Project window, at the left side of the Editor, double-click on the name of the worksheet that you want to affect. (You may need to first open the VBAProject folder, and then open the Microsoft Excel Objects folder under it.) The code window for the worksheet should appear.
3. In the code window, use the right pull-down list (there are two pull-down lists just below the title bar of the code window) to select the Activate option. The following code should appear in the code window:

     Private Sub Worksheet_Activate()

     End Sub

1. In this new subroutine enter the macro you want executed whenever the worksheet is activated.
2. Close the VBA Editor.

Remember that a macro defined in this way is run every time the worksheet is activated, not just the first time. Think about how you use Excel; it is possible to activate a worksheet several dozen times during the course of a session.