Activate and deactivate events in a Workbook

The previous examples use events associated with a worksheet. The ThisWorkbook object also handles events that deal with sheet activation and deactivation. The following procedure, which is stored in the Code window for the ThisWorkbook object, is executed when any sheet in the workbook is activated. The code displays a message with the name of the activated sheet.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

MsgBox Sh.Name End Sub

The Workbook_SheetActivate procedure uses the Sh argument. Sh is a variable that represents the active Sheet object. The message box displays the Sheet object's Name property.

The next example is contained in a ThisWorkbook Code window. It consists of two event-handler procedures. Workbook_SheetDeactivate is executed when a sheet is deactivated. It stores the sheet that is deactivated in an object variable. (The Set keyword creates an object variable.) The Workbook_SheetActivate code checks the type of sheet that is activated (using the TypeName function). If the sheet is a chart sheet, the user gets a message and the previous sheet (which is stored in the OldSheet variable) is reactivated. The effect is that users cannot activate a chart sheet (and are always returned to the previous sheet if they try).

A workbook that contains this code is available at this book's Web site.

Dim OldSheet As Object

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

Set OldSheet = Sh End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object) If TypeName(Sh) = "Chart" Then

MsgBox "Sorry, you can't activate any charts." OldSheet.Activate End If End Sub

0 0

Post a comment