Running a Macro when Deactivating a Worksheet

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 is 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. 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.