VBA Programming Series...44

Are events useful?

At this point, you may be wondering how these events can be useful. Here’s a quick example.

Suppose you have a workbook in which you enter data in column A. Your boss tells you that he needs to know exactly when each data point was entered. Entering data is an event — a WorksheetChange event. You can write a macro that responds to this event. That macro kicks in whenever the worksheet is changed. If the change was made in column A, the macro puts the date and time in column B, next to the data point that was entered.

In case you’re curious, here’s what such a macro would look like. (It should be in the Code module for the worksheet.) Probably a lot simpler than you thought it would be, eh?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then

Target.Offset(0, 1) = Now

End If

End Sub

Just because your workbook contains procedures that respond to events doesn’t guarantee that those procedures will actually run. As you know, it’s possible to open a workbook with macros disabled. In such a case, all macros (even procedures that respond to events) are turned off. Keep this fact in mind when you create workbooks that rely on event-handler procedures.

Programming event-handler procedures

A VBA procedure that executes in response to an event is called an event-handler procedure. These are always Sub procedures (as opposed to Function procedures). Writing these event-handlers is relatively straightforward after you understand how the process works. It boils down to a few steps, all of which I explain later:

1. Identify the event you want to trigger the procedure.

2. Press Alt+F11 to Activate the Visual Basic Editor.

3. In the VBE Project Window, double-click the appropriate object listed under Microsoft Excel Objects.

For workbook-related events, the object is ThisWorkbook. For a work-sheet-related event, the object is a Worksheet object (such as Sheet1).

4. In the Code window for the object, write the event-handler procedure that is executed when the event occurs.

This procedure will have a special name that identifies it as an event-handler procedure.

Where Does the VBA Code Go?

It’s very important to understand where your event-handler procedures go. They must reside in the Code window of an Object module. They do not go in a standard VBA module. If you put your event-handler procedure in the wrong place, it simply won’t work. And you won’t see any error messages either.

Figure shows the VBE window with one project displayed in the Project window. Notice that the VBA project for Book1 is fully expanded and consists of several objects:

✓ One object for each worksheet in the workbook (in this case, three Sheet objects)

✓ An object labeled ThisWorkbook

✓ A VBA module that I inserted manually by using the Insert➪Module command

Double-clicking any of these objects displays the code associated with the item, if any.

The event-handler procedures that you write go into the Code window for the ThisWorkbook item (for workbook-related events) or one of the Sheet objects (for worksheet-related events).

In Figure, the Code window for the Sheet1 object is displayed, and it hap-pens to have a single event-handler procedure defined. Notice the two drop-down controls at the top of the Code module? Keep reading to find out why those are useful.

Writing an Event-Handler Procedure

The VBE helps you out when you’re ready to write an event-handler proce-dure; it displays a list of all events for the selected object.

At the top of each Code window, you find two drop-down lists:

✓ The Object drop-down list (the one on the left)

✓ The Procedure drop-down list (the one on the right)

By default, the Object drop-down list in the Code window displays General. If you’re writing an event-handler for the ThisWorkbook object, you need to choose Workbook from the Object drop-down (it’s the only other choice).

If you’re writing an event-handler for a Sheet object, you need to choose Worksheet (again, the only other choice).

After you’ve made your choice from the Object drop-down list, then you can choose the event from the Procedure drop-down list. Figure shows the choices for a workbook-related event.

When you select an event from the list, VBE automatically starts creating an event-handler procedure for you. This is a very useful feature, because you can verify that the proper arguments are used.

Here’s a little quirk. When you first selected Workbook from the Object list, VBE always assumes that you want to create an event-handler procedure for the Open event and creates it for you. If you’re actually creating a Workbook_ Open procedure, that’s fine. But if you’re creating a different event-procedure, you need to delete the empty Workbook_Open Sub that Excel created.

VBE’s help goes only so far, however. It writes the Sub statement and the End Sub statement. Writing the VBA code that goes between these two state-ments is your job.

You don’t really have to use those two drop-downs, but it makes your job easier because the name of the event-handler procedure is critically impor-tant. If you don’t get the name exactly right, it won’t work. Plus, some event-handler procedures use one or more arguments in the Sub statement. There’s no way you can remember what those arguments are. For example, if you select SheetActivate from the event list for a Workbook object, VBE writes the following Sub statement:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

In this case, Sh is the argument passed to the procedure and is a variable that represents the sheet in the activated workbook. 

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) drop-down list.

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

7. Enter the following statements, so the complete event-procedure looks like this:

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.

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:

✓ Displaying welcome messages (such as in Frank’s cool workbook) ✓ Opening other workbooks

✓ Activating a particular worksheet in the workbook ✓ Setting up custom shortcut menus

The BeforeClose event for a workbook

Here’s an example of the Workbook_BeforeClose event-handler procedure, which is executed automatically immediately before the workbook is closed. This procedure is located in the Code window for a ThisWorkbook object:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Msg As String

Dim Ans As Integer

Dim FName As String

Msg = “Would you like to make a backup of this file?”

Ans = MsgBox(Msg, vbYesNo)

If Ans = vbYes Then

FName = “F:\BACKUP\” & ThisWorkbook.Name

ThisWorkbook.SaveCopyAs FName

End If

End Sub

This routine uses a message box to ask the user whether he would like to make a backup copy of the workbook. If the answer is yes, the code uses the SaveCopyAs method to save a backup copy of the file on drive F. If you adapt this procedure for your own use, you probably need to change the drive and path.

Excel programmers often use a Workbook_BeforeClose procedure to clean up after themselves. For example, if you use a Workbook_Open procedure to change some settings when you open a workbook (hiding the status bar, for example), it’s only appropriate that you return the settings to their original state when you close the workbook. You can perform this electronic house-keeping with a Workbook_BeforeClose procedure.

There is an unfortunate caveat with the Workbook_BeforeClose event. If you close Excel and any open file has been changed since the last save, Excel will show its usual “Do you want to save changes” message box. Clicking the Cancel button cancels the entire closing process. But the Workbook_ BeforeClose event will have been executed anyway.

The BeforeSave event for a workbook

The BeforeSave event, as its name implies, is triggered before a workbook is saved. This event occurs when you use either the File➪Save or File➪Save As command.

The following procedure, which is placed in the Code window for a ThisWorkbook object, demonstrates the BeforeSave event. The routine updates the value in a cell (cell A1 on Sheet1) every time the workbook is saved. In other words, cell A1 serves as a counter to keep track of the number of times the file was saved.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI _

As Boolean, Cancel As Boolean)

Sheets(“Sheet1”).Range(“A1”).Value = _

Sheets(“Sheet1”).Range(“A1”).Value +1

End Sub

Notice that the Workbook_BeforeSave procedure has two arguments, SaveAsUI and Cancel. To demonstrate how these arguments work, examine the following macro, which is executed before the workbook is saved. This procedure prevents the user from saving the workbook with a different name. If the user chooses the File➪Save As command, then the SaveAsUI argument is True.

When the code executes, it checks the SaveAsUI value. If this variable is True, the procedure displays a message and sets Cancel to True, which cancels the Save operation.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI _

As Boolean, Cancel As Boolean)

If SaveAsUI Then

MsgBox “You cannot save a copy of this workbook!”

Cancel = True

End If

End Sub

Keep in mind that this procedure won’t really prevent anyone from saving a copy with a different name. If someone really wants to do it, they can just open the workbook with macros disabled. When macros are disabled, event-handler procedures are also disabled — which makes sense because they are, after all, macros.

Examples of Activation Events

Another category of events consists of activating and deactivating objects — specifically, sheets and workbooks.

Activate and deactivate events in a sheet

Excel can detect when a particular sheet is activated or deactivated and execute a macro when either of these events occurs. These event-handler procedures go in the Code window for the Sheet object.

You can quickly access a sheet’s code window by right-clicking on the sheet’s tab and selecting View Code.

The following example shows a simple procedure that is executed whenever a particular sheet is activated. This code simply pops up a message box that displays the name of the active sheet:

Private Sub Worksheet_Activate()

MsgBox “You just activated “ & ActiveSheet.Name

End Sub

Here’s another example that activates cell A1 whenever the sheet is activated:

Private Sub Worksheet_Activate()

Range(“A1”).Activate

End Sub

Although the code in these two procedures is about as simple as it gets, event-handler procedures can be as complex as you like.

The following procedure (which is stored in the Code window for the Sheet1 object) uses the Deactivate event to prevent a user from activating any other sheet in the workbook. If Sheet1 is deactivated (that is, another sheet is acti-vated), the user gets a message and Sheet1 is activated.

Private Sub Worksheet_Deactivate()

MsgBox “You must stay on Sheet1”

Sheets(“Sheet1”).Activate

End Sub

By the way, I don’t recommend using procedures, such as this one, that attempt to take over Excel. These so-called “dictator” applications can be very frustrating and confusing for the user. Rather, I recommend training the user how to use your application correctly.

Activate and deactivate events in a workbook

The previous examples use events associated with a specific worksheet. The ThisWorkbook object also handles events that deal with sheet activation and deactivation. The following procedure, which is stored in the Code window for the ThisWorkbook object, is executed when any sheet in the workbook is activated. The code displays a message with the name of the activated sheet.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

MsgBox Sh.Name

End Sub

The Workbook_SheetActivate procedure uses the Sh argument. Sh is a vari-able that represents the active Sheet object. The message box displays the Sheet object’s Name property. The next example is contained in a ThisWorkbook Code window. It consists of two event-handler procedures:

✓ Workbook_SheetDeactivate: 

Executed when any sheet in the workbook is deactivated. It stores the sheet that is deactivated in an object vari-able. (The Set keyword creates an object variable.)

✓ Workbook_SheetActivate: 

Executed when any sheet in the workbook is activated. It checks the type of sheet that is activated (using the TypeName function). If the sheet is a chart sheet, the user gets a mes-sage (see Figure). When the OK button in the message box is clicked, the previous sheet (which is stored in the OldSheet variable) is reactivated.

A workbook that contains this code is available at this book’s Web site.

Dim OldSheet As Object

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

Set OldSheet = Sh

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim Msg As String

If TypeName(Sh) = “Chart” Then

Msg = “This chart contains “

Msg = Msg & ActiveChart.SeriesCollection(1).

Points.Count

Msg = Msg & “ data points.” & vbNewLine

Msg = Msg & “Click OK to return to “ & OldSheet.

Name

MsgBox Msg

OldSheet.Activate

End If

End Sub

Workbook activation events

Excel also recognizes the event that occurs when you activate or deactivate a particular workbook. The following code, which is contained in the Code window for the ThisWorkbook object, is executed whenever the workbook is activated. The procedure simply maximizes the workbook’s window.

Private Sub Workbook_Activate()

ActiveWindow.WindowState = xlMaximized

End Sub

The Workbook_Deactivate code, shown next, is executed when a workbook is deactivated. Here’s an example procedure that copies the selected range. It might be useful if you’re copying data from lots of different areas and pasting them to a different workbook. Select the range, activate the other workbook, select the destination, and press Ctrl+V to paste the copied data.

Private Sub Workbook_Deactivate()

ThisWorkbook.Windows(1).RangeSelection.Copy

End Sub

Simple as it is, this procedure required some experimentation before I got it to work correctly. First I tried this:

Selection.Copy

This statement didn’t work as I had intended. It copied the range from the second workbook (the one I activated after deactivating the first workbook). That’s because the second workbook became the active workbook after the deactivation event occurred.

This statement didn’t work either. In fact, it gave me a runtime error:

ThisWorkbook.ActiveSheet.Selection.Copy

I eventually remembered the RangeSelection property of a Window object.

And that one did the trick.