Running a Macro When a Worksheet is Activated

It is possible to configure Excel so that a macro of your choosing is executed every time a particular worksheet is activated. What does that mean? Simply that a macro can be run every time you click on the tab for a worksheet and display it. All you need to do is follow these steps if you are using Excel 2007:

1. Activate the worksheet with which you want the macro associated.
2. Make sure the Formulas tab of the ribbon is displayed.
3. In the Defined Names area of the ribbon, click Define Name. Excel displays the New Name dialog box. (Click here to see a related figure.)
4. In the Name field, enter a name that begins with the worksheet name, followed by an exclamation point, Auto_Activate, and any other wording desired. Thus, if the worksheet were named Stocks, you might enter Ledger!Auto_Activate_ALW.
5. In the Refers to box, enter a formula that points to the workbook and macro you want automatically executed. Thus, if the macro name were Update_Accts, and the workbook name were SALES.XLS, you would enter the formula =Sales!Update_Accts.
6. Click on the OK button.

For older versions of Excel, do the following:

1. Activate the worksheet with which you want the macro associated.
2. Choose Name from the Insert menu. You will see a submenu.
3. Choose Define from the submenu. You will then see the Define Name dialog box.
4. In the Names in Workbook field, enter a name that begins with the worksheet name, followed by an exclamation point, Auto_Activate, and any other wording desired. Thus, if the worksheet were named Ledger, you might enter Ledger!Auto_Activate_ALW.
5. In the Refers to field, enter a formula that points to the workbook and macro you want automatically executed. Thus, if the macro name were Update_Accts, and the workbook name were SALES.XLS, you would enter the formula =Sales!Update_Accts.
6. Click on the OK button.

Remember that a procedure 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.