The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

Validation.Add Method

Office 2007
Adds data validation to the specified range.


expression.Add(Type, AlertStyle, Operator, Formula1, Formula2)

expression   A variable that represents a Validation object.


NameRequired/OptionalData TypeDescription
TypeRequiredXlDVTypeThe validation type.
AlertStyleOptionalVariantThe validation alert style. Can be one of the following XlDVAlertStyle constants: xlValidAlertInformation, xlValidAlertStop, or xlValidAlertWarning.
OperatorOptionalVariantThe data validation operator. Can be one of the following XlFormatConditionOperator constants: xlBetween, xlEqual, xlGreater, xlGreaterEqual, xlLess, xlLessEqual, xlNotBetween, or xlNotEqual.
Formula1OptionalVariantThe first part of the data validation equation.
Formula2OptionalVariantThe second part of the data validation when Operator is xlBetween or xlNotBetween (otherwise, this argument is ignored).


The Add method requires different arguments, depending on the validation type, as shown in the following table.

Validation typeArguments
xlValidateCustomFormula1 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.
xlInputOnlyAlertStyle, Formula1, or Formula2 are used.
xlValidateListFormula1 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 xlValidateTimeOne of either Formula1 or Formula2 must be specified, or both may be specified.


This example adds data validation to cell E5.

Visual Basic for Applications
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

Community Additions

© 2014 Microsoft