The Validation Object

A Validation object is used to enforce data validation on a cell or range of cells. The Validation property of the Range object returns a Validation object, whose properties can be returned or set. Note that there is no Validations collection.

Data validation involves three parts: the actual validation, an input message that can be displayed when a cell is activated, and an error message that can be displayed if the data entered is invalid.

The methods of the Validation object are Add, Delete, and Modify. To add validation to a range, use the Add method, whose syntax is:

ValidationObject.Add(Type, AlertStyle, Operator, Formulal, Formula2)

Note the similarity between the parameters of the Add method of the Validation object and the Add method of the FormatConditions object.

The required Type parameter specifies the type of data allowed and can be one of the following XlDVType constants:

Enum XlDVType xlValidatelnputOnly = 0 xlValidateWholeNumber = 1 xlValidateDecimal = 2 xlValidateList = 3 xlValidateDate = 4 xlValidateTime = 5 xlValidateTextLength = 6 xlValidateCustom = 7 End Enum

The xlValidateInputOnly constant causes Excel to treat all data as valid. This value should be used when we want to display an input message (described later in this section), but not invoke data validation.

The optional AlertStyle parameter specifies the buttons that will appear on the error dialog box that is displayed if the data entered is invalid. It can be one of the following

XlDVAlertStyle constants:

Enum XlDVAlertStyle xlValidAlertStop = 1 xlValidAlertWarning = 2 xlValidAlertInformation = 3 End Enum

The meanings of these constants are as follows: xlValidAlertInformation

OK and Cancel buttons xlValidAlertStop

Retry and Cancel buttons xlValidAlertWarning

Yes, No, and Cancel buttons

The optional Operator parameter is the operator used in the validation, and can be any one of the XlFormatConditionOperator constants:

Enum XlFormatConditionOperator xlBetween = 1 xlNotBetween = 2 xlEqual = 3 xlNotEqual = 4 xlGreater = 5 xlLess = 6 xlGreaterEqual = 7 xlLessEqual = 8 End Enum

The Formula1 parameter specifies the first part of the data-validation equation and Formula2 specifies the second part when Operator is xlBetween or xlNotBetween.

To understand this rather complex object, it is best to look at the corresponding dialog boxes in the Excel user interface. Figure 19-25 shows the Settings tab of the Validation dialog box.

Figure 19-25. The Settings tab of the Data Validation dialog

This dialog corresponds to setting:

Type:=xlValidateWholeNumber Operator:=xlBetween Formula1:="5" Formula2:="10" IgnoreBlank = True

You can learn more about the Type constants by clicking on the ? button in the Data Validation dialog and then clicking on the Allow drop-down list box. Note that the other controls on the tab in Figure 19-25 will change depending upon the value selected in the Allow drop-down box.

The Input Message tab is shown in Figure 19-26. The values in this dialog correspond to properties of the Validation object. In particular, we have:

ShowInput = True InputTitle = "Input:" InputMessage = "Input a number"

Figure 19-26. The Input Message tab of the Data Validation dialog

Tea2H ly®

Dntfl Vnlidnlitii)

s&ttingî Tnput Wesage I ; Error ftlart |

j? 3io»v nput mtissqa wheii cell rs-selected When cell is selected, sho^v ths input message Tjtle-

^ar All

Input nnessage:

input ^fwTiber

J

Cancel

Figure 19-27 shows the Error Alert tab. This dialog corresponds to the following properties of the Validation object:

ShowError = True ErrorTitle = "Error:" ErrorMessage = "This is an error"

Figure 19-27. The Error Alert tab of the Data Validation dialog

DiiIa Vi-il 11 i m ti i ) ri setting: | Jnput Menage- Fircffilart

I? 5ho^.- errci aiertafo» iivajd ciaia issmaradj When Liser enters invalid date, show this error al&"t 9ye: Title:

Ljror message:

This s a -^rrcr

J

Cancel

We can now put all of the pieces together to show how to set up data validation for a range of cells. Note that the first order of business is to delete any old validation:

With Range("A1:D4").Validation .Delete

.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="5", Formula2:="10" .IgnoreBlank = True .ShowInput = True .InputTitle = "Input:" .InputMessage = "Input a number"

.ShowError = True .ErrorTitle = "Error:" .ErrorMessage = "This is a error" End With

0 -1

Post a comment