Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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. |
As in the Data Validation dialog box, Formula1 and Formula2 can't contain a structured reference to a table, such as =Table1[Category], =COUNTIF(Table1[Category],A2)>0, or =MAX(Table1[Amount]). Passing a structured table reference causes runtime error 1004 ("Application-defined or object-defined error"), even though the same reference works in a worksheet formula.
To base a rule on a table column, refer to the column through a defined name, as shown in the second example. For example, define a name CategoryList that refers to =Table1[Category] (on the Formulas tab, choose Define Name), and pass "=CategoryList" as Formula1. The name is updated when the table or the column is renamed, and the list grows when rows are added to the table.
If you can't use a defined name, you can pass the reference as text to the INDIRECT function, for example "=INDIRECT(""Table1[Category]"")". Because INDIRECT refers to the table and the column by text, the rule rejects every value after either is renamed. INDIRECT is also a volatile function.
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
This example adds a drop-down list to cell D2 on Sheet1 whose items are the values in the Category column of the table Table1. It assumes that the workbook has a defined name CategoryList that refers to =Table1[Category]. Passing "=Table1[Category]" directly as Formula1 would cause run-time error 1004.
With Worksheets("Sheet1").Range("D2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=CategoryList"
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.