Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Running a Macro When a Worksheet is Deactivated.
by Allen Wyatt
(last updated May 30, 2015)
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:
Figure 1. The New Name dialog box.
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.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6138) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Running a Macro When a Worksheet is Deactivated.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Need to know the current hour of the day? You can derive the information in your macros by using the Hour function, as ...Discover More
You can easily add a button to your worksheet that will allow you to run various macros. This tip shows how easy it is.Discover More
If you need to limit the cells that are accessible by the user of a worksheet, VBA can come to the rescue. This doesn't ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."