Running a Macro When a Worksheet is Deactivated

It is possible to configure Excel so that a macro of your choosing is executed every time a particular worksheet is deactivated. What does that mean? Simply that a macro can be run every time you click on a worksheet tab to leave the current sheet. All you need to do is follow these steps:

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 see the Define Name dialog box. (Click here to see a related figure.)
4. In the Names in Workbook field, enter a name that begins with the worksheet name, followed by an exclamation point, Auto_Deactivate, and any other wording desired. Thus, if the worksheet were named Stocks, you might enter Stocks!Auto_Deactivate_Exit.
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_PL, and the workbook name were PFOLIO.XLS, you would enter the formula =PFolio!Update_PL.
6. Click on the OK button.

If you are using Excel 2007, then you define the requisite macro names in this manner:

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_Deactivate, and any other wording desired. Thus, if the worksheet were named Stocks, you might enter Stocks!Auto_Deactivate_Exit.
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_PL, and the workbook name were PFOLIO.XLS, you would enter the formula =PFolio!Update_PL.
6. Click on the OK button.

Remember that a macro defined in this way is run every time the worksheet is deactivated, not just the first time. Think about how you use Excel; if you spend a fair amount of time hopping between worksheets in a workbook or between workbooks, it is possible to deactivate a worksheet several dozen times during the course of a session.