VBA Programming Series...45

Other Worksheet-Related Events

In the preceding section, I present examples for worksheet activation and deactivation events. In this section, I discuss three additional events that occur in worksheets: double-clicking a cell, right-clicking a cell, and changing a cell.

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 in that sheet 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

The Worksheet_BeforeDoubleClick procedure has two arguments: Target and Cancel. Target represents the cell (a Range object) that was double-clicked. If Cancel is set to True, the default double-click action doesn’t occur.

The default action for double-clicking a cell is to put Excel into cell edit mode.

I didn’t want that to happen, so I set Cancel to True.

The BeforeRightClick event

The BeforeRightClick event is similar to the BeforeDoubleClick event, except that it consists of right-clicking a cell. The following 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.CommandBars.ExecuteMso _

(“NumberFormatsDialog”)

Cancel = True

End If

End Sub

This is because VBA considers empty cells to be numeric. Don’t ask me why; it just does.

The Change event

The Change event occurs whenever any cell on the worksheet is changed. In the following example, the Worksheet_Change procedure effectively prevents a user from entering a nonnumeric value into cell A1. This code is stored in the Code window for a Sheet object.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = “$A$1” Then

If Not IsNumeric(Target) Then

MsgBox “Enter a number in cell A1.”

Range(“A1”).ClearContents

Range(“A1”).Activate

End If

End If

End Sub

The single argument for the Worksheet_Change procedure (Target) repre-sents the range that was changed. The first statement sees whether the cell’s address is $A$1. If so, the code uses the IsNumeric function to determine whether the cell contains a numeric value. If not, a message appears, and the cell’s value is erased. Cell A1 is then activated — useful if the cell pointer moved to a different cell after the entry was made. If the change occurs in any cell except A1, nothing happens.

Why not use data validation?

You may be familiar with the Data➪Data Tools➪Data Validation com-mand. This is a handy feature that makes it easy to ensure that only data of the proper type is entered into a particular cell or range. Although the

Data➪Data Tools➪Data Validation command is useful, it’s definitely not foolproof. To demonstrate, start with a blank worksheet and perform the following steps:

1. Select the range A1:C12.

2. Choose Data➪Data Tools➪Data Validation.

3. Set up your validation criteria to accept only whole numbers between 1 and 12, as shown in Figure 11-5.

Now, enter some values in the range A1:C12. The data validation works as it should. But to see it fall apart at the seams, try this:

1. Enter –1 into any cell outside the validation range (any cell not in

A1:C12).

2. Press Ctrl+C to copy the negative number to the Clipboard.

3. Select any cell in the validation range.

4. Press Ctrl+V to paste the copied value.

You find that the paste operation is allowable. Look a little closer, however, and you find that the cell into which you pasted the negative value no longer has any validation criteria. Pasting wipes out the data validation criteria!

The severity of this flaw depends on your application. In the next section, I describe how to use the Change event to provide for better validating.

Pasting wipes out data validation because Excel considers validation a format for a cell. Therefore, it is in the same classification as font size, color, or other similar attributes. When you paste a cell, you are replacing the formats in the target cell with those of the source cell. Unfortunately, those formats also include your validation rules.

Preventing data validation from being destroyed

The procedure in this section demonstrates how to prevent users from copy-ing data and wiping out data validation rules. This example assumes that the worksheet has a range named InputArea, and this input area contains data validation rules (set up by using the Data➪Data Tools➪Data Validation com-mand). The range can have any validation rules you want.

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

The procedure is executed whenever a cell is changed. It checks the valida-tion type of the range (named InputRange) that is supposed to contain the data validation rules. If the VT variable contains an error, that means that one or more cells in the InputRange no longer have data validation (the user probably copied some data over it). If that’s the case, the code executes the Undo method of the Application object and reverses the user’s action. Then it displays a message box.

The net effect? It’s impossible to wipe out the validation rules by copying data. When Excel is broken, use VBA to fix it.