Validation.Add Method (Excel)
Adds data validation to the specified range.
Syntax
expression .Add(Type, AlertStyle, Operator, Formula1, Formula2)
expression A variable that represents a Validation object.
Parameters
Name |
Required/Optional |
Data Type |
Description |
|---|---|---|---|
Type |
Required |
XlDVType |
The validation type. |
AlertStyle |
Optional |
Variant |
The validation alert style. Can be one of the following XlDVAlertStyle constants: xlValidAlertInformation, xlValidAlertStop, or xlValidAlertWarning. |
Operator |
Optional |
Variant |
The data validation operator. Can be one of the following XlFormatConditionOperator constants: xlBetween, xlEqual, xlGreater, xlGreaterEqual, xlLess, xlLessEqual, xlNotBetween, or xlNotEqual. |
Formula1 |
Optional |
Variant |
The first part of the data validation equation. |
Formula2 |
Optional |
Variant |
The second part of the data validation when Operator is xlBetween or xlNotBetween (otherwise, this argument is ignored). |
Remarks
The Add method requires different arguments, depending on the validation type, as shown in the following table.
Validation type |
Arguments |
|---|---|
xlValidateCustom |
Formula1 is required, Formula2 is ignored. Formula1 must contain an expression that evaluates to True when data entry is valid and False when data entry is invalid. |
xlInputOnly |
AlertStyle, Formula1, or Formula2 are used. |
xlValidateList |
Formula1 is required, Formula2 is ignored. Formula1 must contain either a comma-delimited list of values or a worksheet reference to this list. |
xlValidateWholeNumber, xlValidateDate, xlValidateDecimal, xlValidateTextLength, or xlValidateTime |
One of either Formula1 or Formula2 must be specified, or both may be specified. |
Example
This example adds data validation to cell E5.
With Range("e5").Validation
.Add Type:=xlValidateWholeNumber, _
AlertStyle:= xlValidAlertStop, _
Operator:=xlBetween, Formula1:="5", Formula2:="10"
.InputTitle = "Integers"
.ErrorTitle = "Integers"
.InputMessage = "Enter an integer from five to ten"
.ErrorMessage = "You must enter a number from five to ten"
End With