Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Triggering an Event when a Worksheet is Deactivated.
Written by Allen Wyatt (last updated November 10, 2018)
This tip applies to Excel 97, 2000, 2002, and 2003
John needs to ensure that certain actions have taken place (information added, etc.) before a user leaves a worksheet. He wonders if there is there some sort of macro event such as WorksheetBeforeDeactivate.
There are actually two events you could use for this purpose. You can use the SheetDeactivate event in the ThisWorkbook module to trigger actions whenever a user leaves any worksheet in the workbook:
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here:
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
MsgBox Prompt:="You just left sheet:" & Sh.Name
End Sub
If you want to trigger actions only when they leave a particular worksheet, then you can use the Deactivate event in the WorkSheet object:
Private Sub Worksheet_Deactivate()
' sheet specific code goes here
End Sub
You should know, however, that in either case the worksheet to which the user is choosing to go will be the active worksheet after the event is completed. If you want to force the user to stay on the worksheet, you need to specifically put them back on the worksheet, in this manner:
Private Sub Worksheet_Deactivate()
' sheet specific code goes here
Sheets("Sheet1").Select
End Sub
This assumes, of course, that the name of the worksheet you want the user to remain on is Sheet1.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7728) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Triggering an Event when a Worksheet is Deactivated.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
When writing macros, you may want to position a message box at a specific location on the screen. This can't be done in ...
Discover MoreWhat is a macro? Ever wonder what these are and how to use them? This tip answers the basics of what a macro is used for, ...
Discover MoreCopying worksheets (one or many) is easy to do manually. What is not well known is that it is even easy to make the ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-03-23 12:27:09
Willy Vanhaelen
@Tobias
The macro does excactly what the code tells it to do:
- ignore the line starting with an apostrophe: 'sheet specific code goes here
- select sheet1
- End Sub = stop
If you want the macro to do something you must replace the first line with the code you want it to execute when Sheet2 is deactivated.
For example : MsgBox "you just left Sheet2"
Be aware that, being in Sheet2, you select another sheet, that sheet is first activated and then your code in Sheet2 is executed.
If you only have 2 sheets, as in your example, the last line: Sheets("Sheet1").Select has no sence since you can only leave Sheet2 by selecting Sheet1. So the macro doesn't have to select Sheet1: you already did -:)
2019-03-22 07:42:48
Tobias
The code above, namely;
Private Sub Worksheet_Deactivate()
' sheet specific code goes here
Sheets("Sheet1").Select
End Sub
does not work as advertised when tested using Excel 2010 - in a new workbook with 2 sheets; happy to send the example I created.
In the example when you select Sheet1 (coming from Sheet2), the code fires but Sheet1 is activated and it stops there ?
Good resource by the way, Thanks
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments