Microsoft Office Tutorials and References
In Depth Information
Getting Acquainted with Workbook-Level Events
h Setting up certain automatic features. For example, you can define key combinations
(see “The OnKey event” section, later in this chapter).
h Setting a worksheet’s ScrollArea property (which isn’t stored with the workbook).
h Setting UserInterfaceOnly protection for worksheets so that your code can operate
on protected sheets. This setting is an argument for the Protect method and isn’t
stored with the workbook.
Creating event-handler procedures doesn’t guarantee that they will be executed. If the
user holds down the Shift key when opening a workbook, the workbook’s Workbook_
Open procedure won’t execute. And, of course, the procedure won’t execute if the
workbook is opened with macros disabled.
Following is an example of a Workbook_Open procedure. It uses VBA’s Weekday function to
determine the day of the week. If it’s Friday, a message box appears, reminding the user to
perform a weekly file backup. If it’s not Friday, nothing happens.
Private Sub Workbook_Open()
If Weekday(Now) = vbFriday Then
Msg = “Today is Friday. Make sure that you “
Msg = Msg & “do your weekly backup!”
MsgBox Msg, vbInformation
The Activate event
The following procedure is executed whenever the workbook is activated. This procedure simply
maximizes the active window. If the workbook window is already maximized, the procedure has
Private Sub Workbook_Activate()
ActiveWindow.WindowState = xlMaximized
The SheetActivate event
The following procedure is executed whenever the user activates any sheet in the workbook. If
the sheet is a worksheet, the code selects cell A1. If the sheet isn’t a worksheet, nothing happens.
This procedure uses VBA’s TypeName function to ensure that the activated sheet is a worksheet
(as opposed to a chart sheet).