Validation.Modify Method (Excel)
Modifies data validation for a range.
Syntax
expression .Modify(Type, AlertStyle, Operator, Formula1, Formula2)
expression A variable that represents a Validation object.
Parameters
Name |
Required/Optional |
Data Type |
Description |
|---|---|---|---|
Type |
Optional |
Variant |
An XlDVType value that represents the validation type. |
AlertStyle |
Optional |
Variant |
An XlDVAlertStyle value that represents the validation alert style. |
Operator |
Optional |
Variant |
An XlFormatConditionOperator value that represents the data validation operator. |
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 Modify method requires different arguments, depending on the validation type, as shown in the following table.
Validation type |
Arguments |
|---|---|
xlInputOnly |
AlertStyle, Formula1, and Formula2 are not used. |
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. |
xlValidateList |
Formula1 is required; Formula2 is ignored. Formula1 must contain either a comma-delimited list of values or a worksheet reference to the list. |
xlValidateDate, xlValidateDecimal, xlValidateTextLength, xlValidateTime, or xlValidateWholeNumber |
Formula1 or Formula2, or both, must be specified. |
Example
This example changes data validation for cell E5.
Range("e5").Validation _
.Modify xlValidateList, xlValidAlertStop, _
xlBetween, "=$A$1:$A$10"