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:

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 Stocks!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.

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.