The Open event

One of the most common events that is monitored is the Open event for a workbook. This event is triggered when the workbook (or add-in) is opened and executes the Workbook_Open procedure. A Workbook_Open procedure can do almost anything and often is used for tasks such as

♦ Displaying welcome messages.

♦ Opening other workbooks.

♦ Setting up custom menus or toolbars.

♦ Activating a particular sheet or cell.

♦ Ensuring that certain conditions are met. For example, a workbook may require that a particular add-in is installed.

♦ Setting up certain automatic features. For example, you can define key combinations (see "The OnKey event" later in this chapter).

♦ Setting a worksheet's ScrollArea property (which isn't stored with the workbook).

♦ Setting UserlnterfaceOnly protection for worksheets so that your code can operate on protected sheets. This setting is an argument for the Protect method and is not stored with the workbook.

If the user holds down the Shift key when opening a workbook, the workbook's Workbook_Open procedure will not execute.And,of course,the procedure will not execute if the workbook is opened with macros disabled.

Following is a simple 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, vblnformation End If End Sub

0 0

Post a comment