OnSheetActivate and OnSheetDeactivate Properties

An OnSheetActivate event handler runs whenever the user switches to a sheet in an open workbook. Set the OnSheetActivate property for the Application object to run a procedure whenever the user switches to any sheet in the application. Set the OnSheetActivate property for a Workbook to run a procedure whenever the user switches to any sheet in a specific workbook. Set the OnSheetActivate property for a Chart, DialogSheet, Module, or Worksheet object to run a procedure whenever the user switches to a specific sheet in a workbook.

You can use OnSheetActivate to ensure that a toolbar that belongs to the workbook is displayed whenever the user switches to that workbook. For example, the following Auto_Open procedure in a workbook initiates trapping for the OnSheetActivate event for the workbook and runs a procedure that displays an Engineering Analysis toolbar whenever the user switches to the workbook.

Sub Auto_Open()
    ThisWorkbook.OnSheetActivate = "WBActivateHandler"
End Sub

Sub WBActivateHandler()
    If ActiveSheet.Name = "My_Data" Then
        Toolbars("EngAnalysisToolbar").Visible = True
    Else
        Toolbars("EngAnalysisToolbar").Visible = False
    End If
End Sub

Another use for an OnSheetActivate procedure is to display a custom menu bar whenever the user switches to a sheet of a certain type. For example, if the user switches to a chart sheet, the built-in chart menu bar is displayed automatically. If you create a custom chart menu bar, Microsoft Excel doesn't display it unless you create a procedure that runs whenever the user switches to a chart sheet.

Similarly, an OnSheetDeactivate procedure runs whenever the user switches away from a sheet or workbook. OnSheetDeactivate procedures are useful for resetting a customized user interface — for example returning menu bars, toolbars, and view settings to their original states.

Note

The OnSheetActivate and OnSheetDeactivate event handlers don't run when you activate a sheet under program control.

When you activate a workbook, the workbook becomes active before the OnSheetDeactivate procedure in the previous workbook runs. Therefore, using the ActiveSheet or ActiveWorkbook property in the OnSheetDeactivate handler may produce unpredictable results.