Chapter 43: Working with Excel Events

When the user attempts to save the workbook, the Workbook_BeforeSave procedure executes. If the save operation brings up the Save As dialog box, the SaveAsUI variable is TRUE. The preceding procedure checks this variable and displays a message only if the Save As dialog box is displayed. In this case, the message is a reminder about how to name the file.

The BeforeSave event procedure also has a Cancel variable in its argument list. If the procedure sets the Cancel argument to TRUE, the file is not saved.

Using the BeforeClose event

The BeforeClose event occurs before a workbook is closed. This event often is used in conjunction with a Workbook_Open event handler. For example, use the Workbook_Open procedure to initialize items in your workbook, and use the Workbook_BeforeClose procedure to clean up or restore settings to normal before the workbook closes.

If you attempt to close a workbook that hasn’t been saved, Excel displays a prompt that asks whether you want to save the workbook before it closes.

Caution

A problem can arise from this event. By the time the user sees this message, the BeforeClose event has already occurred. This means that the Workbook_BeforeClose procedure has already executed. n

Working with Worksheet Events

The events for a Worksheet object are some of the most useful. As you’ll see, monitoring these events can make your applications perform feats that otherwise would be impossible.

Table 43.2 lists the more commonly used worksheet events, with a brief description of each. Remember that these event procedures must be entered into the code module for the sheet. These code modules have default names like Sheet1, Sheet2, and so on.

TABLE 43.2

continued

879

Part VI: Programming Excel with VBA

TABLE 43.2 (continued)

Using the Change event

A Change event is triggered when any cell in the worksheet is changed by the user. A Change event is not triggered when a calculation generates a different value for a formula or when an object (such as a chart or SmartArt) is added to the sheet.

When the Worksheet_Change procedure executes, it receives a Range object as its Target argument. This Range object corresponds to the changed cell or range that triggered the event. The following example displays a message box that shows the address of the Target range:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

MsgBox “Range “ & Target.Address & “ was changed.”

End Sub

To get a feel for the types of actions that generate the Change event for a worksheet, enter the preceding procedure into the code module for a Worksheet object. After entering this procedure, activate Excel and, using various techniques, make changes to the worksheet. Every time the Change event occurs, a message box displays the address of the range that changed.

Unfortunately, the Change event doesn’t always work as expected. For example

•Changing the formatting of a cell does not trigger the Change event (as expected), but choosing Home Editing Clear Clear Formats does.

•Pressing Delete generates an event even if the cell is empty at the start.

•Cells changed via Excel commands may or may not trigger the Change event. For example, sorting and goal seeking operations do not trigger the Change event. However, operations such as Find and Replace, using the AutoSum button, or adding a Totals row to a table do trigger the event.

•If your VBA procedure changes a cell, it does trigger the Change event.

880

Chapter 43: Working with Excel Events

Monitoring a specific range for changes

Although the Change event occurs when any cell on the worksheet changes, most of the time, you’ll be concerned only with changes that are made to a specific cell or range. When the Worksheet_Change event-handler procedure is called, it receives a Range object as its argument. This Range object corresponds to the cell or cells that changed.

Assume that your worksheet has a range named InputRange, and you want to monitor changes to this range only. No Change event exists for a Range object, but you can perform a quick check within the Worksheet_Change procedure. The following procedure demonstrates this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim VRange As Range

Set VRange = Range(“InputRange”)

If Union(Target, VRange).Address = VRange.Address Then

Msgbox “The changed cell is in the input range.”

End if

End Sub

This example creates a Range object variable named VRange, which represents the worksheet range that you want to monitor for changes. The procedure uses the VBA Union function to determine whether VRange contains the Target range (passed to the procedure in its argument). The Union function returns an object that consists of all the cells in both of its arguments. If the range address is the same as the VRange address, Vrange contains Target, and a message box appears. Otherwise, the procedure ends, and nothing happens.

The preceding procedure has a flaw. Target may consist of a single cell or a range. For example, if the user changes more than one cell at a time, Target becomes a multicell range. Therefore, the procedure requires modification to loop through all the cells in Target. The following procedure checks each changed cell and displays a message box if the cell is within the desired range:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Set VRange = Range(“InputRange”)

For Each cell In Target

If Union(cell, VRange).Address = VRange.Address Then

Msgbox “The changed cell is in the input range.”

End if

Next cell

End Sub

On the CD

A workbook with this example is available On the CD-ROM the file is named monitor a range.xlsm.

881

Part VI: Programming Excel with VBA

Using the SelectionChange event

The following procedure demonstrates a SelectionChange event. It executes whenever the user makes a new selection on the worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target _

As Excel.Range)

Cells.Interior.ColorIndex = xlNone

With ActiveCell

.EntireRow.Interior.ColorIndex = 35

.EntireColumn.Interior.ColorIndex = 35

End With

End Sub

This procedure shades the row and column of an active cell, making it easy to identify. The first statement removes the background color of all cells. Next, the entire row and column of the active cell is shaded light yellow. Figure 43.2 shows the shading.

On the CD

A workbook with this example is available On the CD-ROM. The file is named selection change event.xlsm.

FIGURE 43.2

Moving the cell cursor causes the active cell’s row and column to become shaded.

882