Chapter 43: Working with Excel Events
877
l (^) Displaying welcome messages.
l Opening other workbooks.
l (^) Activating a specific sheet.
l Ensuring that certain conditions are met; for example, a workbook may require that a par-
ticular add-in is installed.
Caution
Be aware 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
workbook’s Workbook_Open procedure will not execute. n
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
What if you would like to activate a particular Ribbon tab automatically when a workbook is
opened? Unfortunately, VBA can’t do much at all with the Excel Ribbon, and there is no direct way
to activate a particular Ribbon tab. The next example uses the SendKeys statement to simulate
keystrokes. In this case, it sends Alt+H, which is the Excel’s “keytip” equivalent of activating the
Home tab of the Ribbon. Sending the F6 keystroke removes the keytip letters from the Ribbon.
Private Sub Workbook_Open()
Application.SendKeys (“%h{F6}”)
End Sub
The following example performs a number of actions when the workbook is opened. It maximizes
the Excel window, maximizes the workbook window, activates the sheet named DataEntry, and
selects the first empty cell in column A. If a sheet named DataEntry does not exist, the code gener-
ates an error.
Private Sub Workbook_Open()
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlMaximized
Worksheets(“DataEntry”).Activate
Range(“A1”).End(xlDown).offset(1,0).Select
End Sub