Introductory Examples

In this section, I provide a few examples so that you can get the hang of this event-handling business.

The Open event for a workbook

One of the most commonly used events is the Workbook Open event. Assume that you have a workbook that you use every day. The Workbook_Open procedure in this example is executed every time the workbook is opened. The procedure checks the day of the week; if it's Friday, the code displays a reminder message for you.

To create the procedure that is executed whenever the Workbook Open event occurs, follow these steps:

1. Open the workbook.

Any workbook will do.

2. Press Alt+F11 to activate the VBE.

3. Locate the workbook in the Project window.

4. Double-click the project name to display its items, if necessary.

5. Double-click the ThisWorkbook item.

The VBE displays an empty Code window for the ThisWorkbook object.

6. In the Code window, select Workbook from the Object (left) dropdown list.

The VBE enters the beginning and ending statements for a Workbook_Open procedure.

7. Enter the following statements:

Private Sub Workbook_Open() Dim Msg As String If WeekDay(Now) = 6 Then

Msg = "Today is Friday. Make sure that you " Msg = Msg & "do your weekly backup!" MsgBox Msg End If End Sub

The Code window should look like Figure 11-4.

Figure 11-4:

The event-handler procedure is executed when the workbook is opened.

Bookl ThisWoikbook (Coile|



▼j Open


Private Sub Workbook Open()

Dim Msg As String

If Weekday(Now) = 6


Msg = "Today is

Friday. Make sure that you

Hsg = Msg & "do

your weekly backup!"

MsgBox Msg

End If



^ <


Workbook_Open is executed automatically whenever the workbook is opened. It uses VBA's WeekDay function to determine the day of the week. If it's Friday (day 6), a message box reminds the user to perform a weekly file backup. If it's not Friday, nothing happens.

If today isn't Friday, you might have a hard time testing this procedure. Here's a chance to test your own skill at VBA. You can modify this procedure any way you like. For example, the following version displays a message every time the workbook is opened. This gets annoying after a while, trust me.

Private Sub Workbook_Open()

Msg = "This is Frank's cool workbook!" MsgBox Msg End Sub

A Workbook_Open procedure can do almost anything. These event-handlers are often used for the following:


1 Displaying welcome messages (such as the one to Frank) 1 Opening other workbooks i Activating a particular worksheet in the workbook 1 Setting up custom menus 1 Displaying or hiding toolbars

Keep in mind that event-handler procedures are not executed if the user disables macros when the workbook is opened. In other words, you can't count on the fact that your event-handler procedures will always work.

0 0

Post a comment