Another category of events consists of activating and deactivating objects — specifically, sheets and windows.
Excel can detect when a particular sheet is activated or deactivated and execute a macro when either of these events occurs. These event-handler procedures go in the Code window for a Sheet object.
The following example shows a simple procedure that is executed whenever a particular sheet is activated. This code simply pops up a message box that displays the name of the active sheet:
Private Sub Worksheet_Activate()
MsgBox "You just activated " & ActiveSheet.Name End Sub
Here's another example that activates cell A1 whenever the sheet is activated:
Private Sub Worksheet_Activate()
Range("A1").Activate End Sub
Although the code in these two procedures is about as simple as it gets, event-handler procedures can be as complex as you like.
The following procedure (which is stored in the Code window for the Sheet1 object) uses the Deactivate event to prevent a user from activating any other sheet in the workbook. If Sheet1 is deactivated (that is, another sheet is activated), the user gets a message and Sheet1 is activated.
Private Sub Worksheet_Deactivate() MsgBox "You must stay on Sheetl" Sheets("Sheet1").Activate End Sub
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)
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
Excel also recognizes the event that occurs when you activate or deactivate a particular workbook. The following code, which is contained in the Code window for the ThisWorkbook object, is executed whenever the workbook is activated. The procedure simply maximizes the workbook's window.
Private Sub Workbook_Activate()
ActiveWindow.WindowState = xlMaximized End Sub
The Workbook_Deactivate code, shown next, is executed when a workbook is deactivated. This procedure minimizes the workbook's window:
Private Sub Workbook_Deactivate()
ThisWorkbook.Windows(1).WindowState = xlMinimized End Sub
Notice that I didn't use ActiveWindow in this code. That's because the workbook is no longer the active window when it's deactivated. Therefore, I used ThisWorkbook, which refers to the workbook that contains the code.
Was this article helpful?