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. Value must not exceed 255 characters. |
| Formula2 | Optional | Variant | The second part of the data validation equation 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
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.