Chapter 46: Working with Excel Events
46
■ (^) Activating a specific sheet
■ Ensuring that certain conditions are met; for example, a workbook may require that
a particular add-in is installed
Keep in mind that there is no guarantee that your Workbook_Open procedure will be executed. For example, the
user may choose to disable macros. And if the user holds down the Shift key while opening a workbook, the work-
book’s Workbook_Open procedure won’t execute.
The following is a simple example of a Workbook_Open procedure. It uses the VBA
Weekday function to determine the day of the week. If it’s Friday, a message box appears
to remind the user to perform a file backup. If it’s not Friday, nothing happens.
Private Sub Workbook_Open()
If Weekday(Now) = 6 Then
Msg = "Make sure you do your weekly backup!"
MsgBox Msg, vbInformation
End If
End Sub
The following example performs a number of actions when the workbook is opened. It maxi-
mizes the workbook window, activates the sheet named DataEntry, selects the first empty
cell in column A, and enters the current date into that cell. If a sheet named DataEntry
does not exist, the code generates an error.
Private Sub Workbook_Open()
ActiveWindow.WindowState = xlMaximized
Worksheets("DataEntry").Activate
Range("A1").End(xlDown).Offset(1,0).Select
ActiveCell.Value = Date
End Sub
Using the SheetActivate event
The following procedure executes whenever the user activates a sheet in the workbook. The
code simply selects cell A1. Including the On Error Resume Next statement causes the
procedure to ignore the error that occurs if the activated sheet is a chart sheet:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next
Range("A1").Select
End Sub
An alternative method to handle the case of a chart sheet is to check the sheet type. Use
the Sh argument, which is passed to the procedure:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If TypeName(Sh) = "Worksheet" Then Range("A1").Select
End Sub