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.

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

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

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.

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.

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.

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.

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.