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 non-numeric 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 represents 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 the Excel DataOValidation command?

You may be familiar with the DataOValidation command. 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 DataOValidation 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 DataOValidation.

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

Figure 11-5:

These settings allow only whole numbers between 1 and 12.

Figure 11-5:

These settings allow only whole numbers between 1 and 12.

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. Choose EditOCopy to copy the negative number to the Clipboard.

3. Select any cell in the validation range.

4. Choose EditOPaste.

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. This means 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.

A data validation example

The next procedure demonstrates a better alternative to the Excel DataO Validation command. It ensures that only positive values are entered into the range A1:C12.

A workbook that contains this code is available at this book's Web site:

Private Sub Worksheet_Change(ByVal Target As Range) Dim ValRange As Range Dim cell As Range Dim DataOK As Boolean Dim Msg As String Set ValRange = Range("A1:C12") DataOK = True For Each cell In Target

If Union(cell, ValRange).Address = _ ValRange.Address Then If cell.Value < 0 Then cell.ClearContents DataOK = False End If End If Next cell

If Not DataOK Then

Msg = "Only positive values are acceptable in " Msg = Msg & ValRange.Address MsgBox Msg, vbCritical End If End Sub

The procedure starts by creating an object variable (ValRange) that represents the range to be validated. DataOK is a Boolean variable initially set to True. The For-Next loop examines each cell in Target (which is the cell or range that was changed). I use the Union function to determine whether the cell is contained in ValRange. If so, an If statement determines whether the cell's value is less than 0. If so, the contents are erased and DataOK is set to False.

When all the cells have been checked, another If statement checks the DataOK value. If it was set to False, one or more cells in the changed range were negative. Therefore, the user gets a message. This routine works even when data is copied and pasted to the validation range.

0 0

Post a comment