Worksheet Events | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

The events for a Worksheet object are some of the most useful. Monitoring these events can make your applications perform feats that would otherwise be impossible .

Table 19-2 lists the worksheet events, with a brief description of each.

Table 19-2: WORKSHEET EVENTS
Open table as spreadsheet
Event	Action That Triggers the Event
`Activate`	The worksheet is activated.
`BeforeDoubleClick`	The worksheet is double-clicked.
`BeforeRightClick`	The worksheet is right-clicked.
`Calculate`	The worksheet is calculated (or recalculated).
`Change`	Cells on the worksheet are changed by the user or by an external link.
`Deactivate`	The worksheet is deactivated.
`FollowHyperlink`	A hyperlink on the sheet is clicked.
`PivotTableUpdate`	A pivot table on the sheet is updated.
`SelectionChange`	The selection on the worksheet is changed.

Remember that the code for a worksheet event must be stored in the code module for the specific worksheet.

Tip	To quickly activate the code module for a worksheet, right-click the sheet tab and then choose View Code.

The Change event

The Change event is triggered when any cell in a worksheet is changed by the user or by a VBA procedure. The Change event is not triggered when a calculation generates a different value for a formula or when an object is added to the sheet.

When the Worksheet_Change procedure is executed, it receives a Range object as its Target argument. This Range object represents the changed cell or range that triggered the event. The following procedure is executed whenever the worksheet is changed. It 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 better feel for the types of actions that generate a Change event for a worksheet, enter the preceding procedure in the code module for a Worksheet object. After entering this procedure, activate Excel and make some changes to the worksheet by using various techniques. Every time the Change event occurs, you'll see a message box that displays the address of the range that was changed.

When I ran this procedure, I discovered some interesting quirks . Some actions that should trigger the event don't, and other actions that should not trigger the event do!

Changing the formatting of a cell does not trigger the Change event (as expected). But copying formats using the Paste Special dialog box does trigger the Change event. Choosing the Home Editing Clear Clear Formats command also triggers the event.
Adding, editing, or deleting a cell comment does not trigger the Change event.
Pressing Delete generates an event even if the cell is empty to start with.
Cells that are changed by using Excel commands may or may not trigger the Change event. For example, sorting a range does not trigger the event. But using the spell checker does.
If your VBA procedure changes a cell, it does trigger the Change event.

As you can see from the preceding list, it's not a good idea to rely on the Change event to detect cell changes for critical applications.

Monitoring a specific range for changes

The Change event occurs when any cell on the worksheet is changed. But, in most cases, all you care about are changes 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 represents the cell or cells that were changed.

Assume that your worksheet has a range named InputRange , and you would like to monitor changes made only within this range. There is no Change event for a Range object, but you can perform a quick check within the Worksheet_Change procedure:

 Private Sub Worksheet_Change(ByVal Target As Excel.Range)     Dim MRange As Range     Set MRange = Range("InputRange")     If Not Intersect(Target, MRange) Is Nothing Then _        MsgBox "A changed cell is in the input range." End Sub

This example uses a Range object variable named MRange , which represents the worksheet range that you are interested in monitoring for changes. The procedure uses VBA's Intersect function to determine whether the Target range (passed to the procedure in its argument) intersects with MRange . The Intersect function returns an object that consists of all the cells that are contained in both of its arguments. If the Intersect function returns Nothing , the ranges have no cells in common. The Not operator is used so the expression returns True if the ranges do have at least one cell in common. Therefore, if the changed range has any cells in common with the range named InputRange , a message box is displayed. Otherwise, the procedure ends, and nothing happens.

MONITORING A RANGE TO MAKE FORMULAS BOLD

The following example monitors a worksheet and also makes formula entries bold and non-formula entries not bold.

 Private Sub Worksheet_Change(ByVal Target As Excel.Range)     Dim cell As Range     For Each cell In Target         cell.Font.Bold = cell.HasFormula     Next cell End Sub

Because the object passed to the Worksheet_Change procedure can consist of a multicell range, the procedure loops through each cell in the Target range. If the cell has a formula, it is made bold. Otherwise, the Bold property is set to False .

The procedure works, but it has a problem. What if the user deletes a row or column? In such a case, the Target range consists of a huge number of cells. The For Each loop would take a very long time to examine them all - and it wouldn't find any formulas.

The modified procedure listed next solves this problem by changing the Target range to be the intersection of the Target range and the worksheet's used range. The check to ensure that Target is Not Nothing handles the case in which an empty row or column outside of the used range is deleted.

 Private Sub Worksheet_Change(ByVal Target As Excel.Range)     Dim cell As Range     Set Target = Intersect(Target, Target.Parent.UsedRange)     If Not Target Is Nothing Then         For Each cell In Target             cell.Font.Bold = cell.HasFormula         Next cell     End If End Sub

CD-ROM

This example, named make formulas bold.xlsm , is available on the companion CD-ROM.

Caution

A potentially serious side-effect of using a Worksheet_Change procedures is that it effectively turns off Excel's Undo feature. Excel's Undo stack is destroyed whenever a macro is executed. Using a Worksheet_Change event procedure executes a macro every time the worksheet is changed.

MONITORING A RANGE TO VALIDATE DATA ENTRY

Excel's data validation feature is a useful tool, but it suffers from a potentially serious problem. When you paste data to a cell that uses data validation, the pasted value not only fails to get validated , but it also deletes the validation rules associated with the cell! This fact makes the data validation feature practically worthless for critical applications. In this section, I demonstrate how you can use the Change event for a worksheet to create your own data validation procedure.

CD-ROM

The companion CD-ROM contains two versions of this example. One (named validate entry1.xlsm ) uses the EnableEvents property to prevent cascading Change events; the other (named validate entry2.xlsm ) uses a Static variable. See "Disabling events," earlier in this chapter.

The Worksheet_Change procedure that follows is executed when a user changes a cell. The validation is restricted to the range named InputRange . Values entered into this range must be integers between 1 and 12.

 Private Sub Worksheet_Change(ByVal Target As Range)     Dim VRange As Range, cell As Range     Dim Msg As String     Dim ValidateCode As Variant     Set VRange = Range("InputRange")     If Intersect(VRange, Target) Is Nothing Then Exit Sub     For Each cell In Intersect(VRange, Target)         ValidateCode = EntryIsValid(cell)         If TypeName(ValidateCode) = "String" Then             Msg = "Cell " & cell.Address(False, False) & ":"             Msg = Msg & vbCrLf & vbCrLf & ValidateCode             MsgBox Msg, vbCritical, "Invalid Entry"             Application.EnableEvents = False             cell.ClearContents             cell.Activate             Application.EnableEvents = True         End If     Next cell End Sub

The Worksheet_Change procedure creates a Range object (named VRange ) that represents the worksheet range that is validated. Then it loops through each cell in the Target argument, which represents the cell or cells that were changed. The code determines whether each cell is contained in the range to be validated. If so, it passes the cell as an argument to a custom function ( EntryIsValid ), which returns True if the cell is a valid entry.

If the entry is not valid, the EntryIsValid function returns a string that describes the problem, and the user is informed via a message box (see Figure 19-6). When the message box is dismissed, the invalid entry is cleared from the cell, and the cell is activated. Notice that events are disabled before the cell is cleared. If events were not disabled, clearing the cell would produce a Change event that causes an endless loop.

Figure 19-6: This message box describes the problem when the user makes an invalid entry.

The EntryIsValid function procedure is shown here:

 Private Function EntryIsValid(cell) As Variant '   Returns True if cell is an integer between 1 and 12 '   Otherwise it returns a string that describes the problem '   Numeric?     If Not WorksheetFunction.IsNumber (cell) Then         EntryIsValid = "Non-numeric entry."         Exit Function     End If '   Integer?     If CInt(cell) <> cell Then         EntryIsValid = "Integer required."         Exit Function     End If '   Between 1 and 12?     If cell < 1 Or cell > 12 Then         EntryIsValid = "Valid values are between 1 and 12."         Exit Function     End If '   It passed all the tests     EntryIsValid = True End Function

The preceding technique works, but it can be rather tedious to set up. Wouldn't it be nice if you could take advantage of Excel's data validation feature, yet ensure that the data validation rules don't get deleted if the user pastes data into the validation range? The next example solves the problem.

 Private Sub Worksheet_Change(ByVal Target As Range)     Dim VT As Long     'Do all cells in the validation range     'still have validation?     On Error Resume Next     VT = Range("InputRange").Validation.Type     If Err.Number <> 0 Then         Application.Undo         MsgBox "Your last operation was canceled." & _         "It would have deleted data validation rules.", vbCritical     End If End Sub

This event procedure checks the validation type of the range (named InputRange ) that is supposed to contains the data validation rules. If the VT variable contains an error, that means that one or more cells in the InputRange no longer contain data validation. In other words, the worksheet change probably resulted from data being copied into the range that contains data validation. If that's the case, the code executes the Undo method of the Application object and reverses the user's action. Then it displays the message box shown in Figure 19-7.

Figure 19-7: The Worksheet_Change procedure ensures that data validation does not get deleted.

Note	A nice side-benefit to using this procedure is that the Undo stack is not destroyed.

CD-ROM

This example, named validate entry3.xlsm , is available on the companion CD-ROM.

The SelectionChange event

The following procedure demonstrates the SelectionChange event. It's executed 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.Color = RGB(219, 229, 241)         .EntireColumn.Interior.Color = RGB(219, 229, 241)     End With End Sub

This procedure shades the row and column of the active cell, which makes it very easy to identify the active cell. The first statement removes the background color for all cells in the worksheet. Next, the entire row and column of the active cell is shaded light blue. Figure 19-8 shows the shading in effect.

Figure 19-8: Moving the cell cursor causes the active cell's row and column to be shaded.

You won't want to use the procedure if your worksheet contains any background shading because it will be wiped out. The exceptions are tables with a style applied and background colors resulting from conditional formatting. In both of these instances, the background color is maintained . Keep in mind, however, that executing the Worksheet_SelectionChange macro destroys the Undo stack, so using this technique essentially disables Excel's Undo feature.

CD-ROM

This example, named shade active row and column.xlsm , is available on the companion CD-ROM.

The BeforeDoubleClick event

You can set up a VBA procedure to be executed when the user double-clicks a cell. In the following example (which is stored in the Code window for a Sheet object), double-clicking a cell makes the cell bold (if it's not bold) or not bold (if it is bold):

 Private Sub Worksheet_BeforeDoubleClick _    (ByVal Target As Excel.Range, Cancel As Boolean)     Target.Font.Bold = Not Target.Font.Bold     Cancel = True End Sub

If Cancel is set to True , the default double-click action doesn't occur. In other words, double-clicking the cell won't put Excel into cell edit mode.

The BeforeRightClick event

When the user right-clicks in a worksheet, Excel displays a shortcut menu. If, for some reason, you'd like to prevent the shortcut menu from appearing in a particular sheet, you can trap the RightClick event. The following procedure sets the Cancel argument to True , which cancels the RightClick event and thereby cancels the shortcut menu. Instead, a message box is displayed.

 Private Sub Worksheet_BeforeRightClick _   (ByVal Target As Excel.Range, Cancel As Boolean)     Cancel = True     MsgBox "The shortcut menu is not available." End Sub

Keep in mind that the user can still access the shortcut menu by using Shift+F10. However, only a tiny percentage of Excel users are aware of that keystroke combination.

CROSS-REFERENCE

To find out how to intercept the Shift+F10 key combination, see "The OnKey event," later in this chapter. Chapter 24 describes other methods for disabling shortcut menus .

Following is another example that uses the BeforeRightClick event. This procedure checks to see whether the cell that was right-clicked contains a numeric value. If so, the code displays the Format Number dialog box and sets the Cancel argument to True (avoiding the normal shortcut menu display). If the cell does not contain a numeric value, nothing special happens - the shortcut menu is displayed as usual.

 Private Sub Worksheet_BeforeRightClick _    (ByVal Target As Excel.Range, Cancel As Boolean)     If IsNumeric(Target) And Not IsEmpty(Target) Then         Application.Dialogs(xlDialogFormatNumber).Show         Cancel = True     End If End Sub

Notice that the code makes an additional check to see if the cell is not empty. This is because VBA considers empty cells to be numeric.

Note	An alternative statement that displays the Format Number dialog box is: Application.CommandBars.ExecuteMso ("NumberFormatsDialog") This statement works only in Excel 2007.