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.
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.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Macros are a great way to expand what you can do with the data you place in Excel. You can make those macros even handier ...
Discover MorePart of the power of VBA is being able to control when some of your code executes and when it doesn't. A primary way to ...
Discover MoreVBA libraries are used to provide some functions to your macros. These libraries are often tied to the version of Excel ...
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 © 2021 Sharon Parq Associates, Inc.
Comments