Set or clear data validation.
| Overload Name | Description |
|---|---|
| Clear | Clear data validation. |
| Custom | The Formula determines whether an entered value is valid. |
| Date | The entered value must be a date. |
| Decimal | The entered value must be a decimal number. |
| InputOnly | Any value is valid. |
| List | Select a valid value from a list. |
| TextLength | A valid entry must contain the specified number of characters. |
| Time | The entered value must be a valid time. |
| WholeNumber | The entered value must be a whole number. |
Clear data validation.
| Argument | Description |
|---|---|
| SpecifySheetBy |
Select how to specify which worksheet to select while the method is being executed.
Specify one of the following values or an arbitrary value:
<blank>SheetNameSheetNumberAllSheets
|
| SheetSpec | Specify the sheet-name or sheet-number, depending on SpecifySheetBy. If SpecifySheetBy is AllSheets then SheetSpec can be left empty. |
| Address | The address of a range to select for the execution of the method. |
| CellByCell | Whether to execute the method on a cell-by-cell basis, or on the range specified by the address. |
| ReverseOrder | Whether to loop through the rows and columns from highest to lowest. Only applies when CellByCell is true. |
| MethodCondition |
If the result of method-condition expression resolves to FALSE, then the method is not executed.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| CellCondition |
If the result of the condition expression resolves to FALSE, then the current cell is skipped.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
The Formula determines whether an entered value is valid.
| Argument | Description |
|---|---|
| SpecifySheetBy |
Select how to specify which worksheet to select while the method is being executed.
Specify one of the following values or an arbitrary value:
<blank>SheetNameSheetNumberAllSheets
|
| SheetSpec | Specify the sheet-name or sheet-number, depending on SpecifySheetBy. If SpecifySheetBy is AllSheets then SheetSpec can be left empty. |
| Address | The address of a range to select for the execution of the method. |
| CellByCell | Whether to execute the method on a cell-by-cell basis, or on the range specified by the address. |
| ReverseOrder | Whether to loop through the rows and columns from highest to lowest. Only applies when CellByCell is true. |
| MethodCondition |
If the result of method-condition expression resolves to FALSE, then the method is not executed.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| CellCondition |
If the result of the condition expression resolves to FALSE, then the current cell is skipped.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| Operator |
The operator to be used for the validation.
Specify one of the following values or an arbitrary value:
<blank>BetweenNot BetweenEqualNot EqualGreater thanGreater than or EqualLess thanLess than or Equal
|
| Formula | The formula that will be used for custom validation. |
| IgnoreBlank |
Specifies whether validation should be skipped if either validation formula refers to a blank cell.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| ShowInputMessage |
Specifies whether an input message should be displayed when one of the represented cells is active.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| InputMessage | The input message displayed when one of the represented cells is active. |
| InputTitle | The input title displayed when one of the represented cells is active. |
| ShowError |
Specifies whether errors should be shown when validation fails.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| AlertStyle |
The alert style used when validation fails.
Specify one of the following values or an arbitrary value:
<blank>InformationStopWarning
|
| ErrorMessage | The error message displayed when validation fails. |
| ErrorTitle | The error title displayed when validation fails. |
The entered value must be a date.
| Argument | Description |
|---|---|
| SpecifySheetBy |
Select how to specify which worksheet to select while the method is being executed.
Specify one of the following values or an arbitrary value:
<blank>SheetNameSheetNumberAllSheets
|
| SheetSpec | Specify the sheet-name or sheet-number, depending on SpecifySheetBy. If SpecifySheetBy is AllSheets then SheetSpec can be left empty. |
| Address | The address of a range to select for the execution of the method. |
| CellByCell | Whether to execute the method on a cell-by-cell basis, or on the range specified by the address. |
| ReverseOrder | Whether to loop through the rows and columns from highest to lowest. Only applies when CellByCell is true. |
| MethodCondition |
If the result of method-condition expression resolves to FALSE, then the method is not executed.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| CellCondition |
If the result of the condition expression resolves to FALSE, then the current cell is skipped.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| Operator |
The operator to be used for the validation.
Specify one of the following values or an arbitrary value:
<blank>BetweenNot BetweenEqualNot EqualGreater thanGreater than or EqualLess thanLess than or Equal
|
| Minimum | The minimum value. |
| Maximum | The maximum value. |
| IgnoreBlank |
Specifies whether validation should be skipped if either validation formula refers to a blank cell.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| ShowInputMessage |
Specifies whether an input message should be displayed when one of the represented cells is active.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| InputMessage | The input message displayed when one of the represented cells is active. |
| InputTitle | The input title displayed when one of the represented cells is active. |
| ShowError |
Specifies whether errors should be shown when validation fails.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| AlertStyle |
The alert style used when validation fails.
Specify one of the following values or an arbitrary value:
<blank>InformationStopWarning
|
| ErrorMessage | The error message displayed when validation fails. |
| ErrorTitle | The error title displayed when validation fails. |
The entered value must be a decimal number.
| Argument | Description |
|---|---|
| SpecifySheetBy |
Select how to specify which worksheet to select while the method is being executed.
Specify one of the following values or an arbitrary value:
<blank>SheetNameSheetNumberAllSheets
|
| SheetSpec | Specify the sheet-name or sheet-number, depending on SpecifySheetBy. If SpecifySheetBy is AllSheets then SheetSpec can be left empty. |
| Address | The address of a range to select for the execution of the method. |
| CellByCell | Whether to execute the method on a cell-by-cell basis, or on the range specified by the address. |
| ReverseOrder | Whether to loop through the rows and columns from highest to lowest. Only applies when CellByCell is true. |
| MethodCondition |
If the result of method-condition expression resolves to FALSE, then the method is not executed.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| CellCondition |
If the result of the condition expression resolves to FALSE, then the current cell is skipped.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| Operator |
The operator to be used for the validation.
Specify one of the following values or an arbitrary value:
<blank>BetweenNot BetweenEqualNot EqualGreater thanGreater than or EqualLess thanLess than or Equal
|
| Minimum | The minimum value. |
| Maximum | The maximum value. |
| IgnoreBlank |
Specifies whether validation should be skipped if either validation formula refers to a blank cell.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| ShowInputMessage |
Specifies whether an input message should be displayed when one of the represented cells is active.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| InputMessage | The input message displayed when one of the represented cells is active. |
| InputTitle | The input title displayed when one of the represented cells is active. |
| ShowError |
Specifies whether errors should be shown when validation fails.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| AlertStyle |
The alert style used when validation fails.
Specify one of the following values or an arbitrary value:
<blank>InformationStopWarning
|
| ErrorMessage | The error message displayed when validation fails. |
| ErrorTitle | The error title displayed when validation fails. |
Any value is valid.
| Argument | Description |
|---|---|
| SpecifySheetBy |
Select how to specify which worksheet to select while the method is being executed.
Specify one of the following values or an arbitrary value:
<blank>SheetNameSheetNumberAllSheets
|
| SheetSpec | Specify the sheet-name or sheet-number, depending on SpecifySheetBy. If SpecifySheetBy is AllSheets then SheetSpec can be left empty. |
| Address | The address of a range to select for the execution of the method. |
| CellByCell | Whether to execute the method on a cell-by-cell basis, or on the range specified by the address. |
| ReverseOrder | Whether to loop through the rows and columns from highest to lowest. Only applies when CellByCell is true. |
| MethodCondition |
If the result of method-condition expression resolves to FALSE, then the method is not executed.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| CellCondition |
If the result of the condition expression resolves to FALSE, then the current cell is skipped.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| ShowInputMessage |
Specifies whether an input message should be displayed when one of the represented cells is active.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| InputMessage | The input message displayed when one of the represented cells is active. |
| InputTitle | The input title displayed when one of the represented cells is active. |
| ShowError |
Specifies whether errors should be shown when validation fails.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| AlertStyle |
The alert style used when validation fails.
Specify one of the following values or an arbitrary value:
<blank>InformationStopWarning
|
| ErrorMessage | The error message displayed when validation fails. |
| ErrorTitle | The error title displayed when validation fails. |
Select a valid value from a list.
| Argument | Description |
|---|---|
| SpecifySheetBy |
Select how to specify which worksheet to select while the method is being executed.
Specify one of the following values or an arbitrary value:
<blank>SheetNameSheetNumberAllSheets
|
| SheetSpec | Specify the sheet-name or sheet-number, depending on SpecifySheetBy. If SpecifySheetBy is AllSheets then SheetSpec can be left empty. |
| Address | The address of a range to select for the execution of the method. |
| CellByCell | Whether to execute the method on a cell-by-cell basis, or on the range specified by the address. |
| ReverseOrder | Whether to loop through the rows and columns from highest to lowest. Only applies when CellByCell is true. |
| MethodCondition |
If the result of method-condition expression resolves to FALSE, then the method is not executed.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| CellCondition |
If the result of the condition expression resolves to FALSE, then the current cell is skipped.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| Source |
The source of the list, which can be a comma-delimited list of values or a sheet range reference. When specifying a comma-delimited list of values, Excel limits the string to 255 characters. When specifying a named range, use the format: ="=NamedRange" (including both equal signs and the double quotes.) When the named range is on a separate sheet, use the format: ="=SheetName!NamedRange" |
| IgnoreBlank |
Specifies whether validation should be skipped if either validation formula refers to a blank cell.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| InCellDropdown |
Specifies whether an in-cell dropdown listbox should be used for validation with a ValidationType of List.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| ShowInputMessage |
Specifies whether an input message should be displayed when one of the represented cells is active.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| InputMessage | The input message displayed when one of the represented cells is active. |
| InputTitle | The input title displayed when one of the represented cells is active. |
| ShowError |
Specifies whether errors should be shown when validation fails.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| AlertStyle |
The alert style used when validation fails.
Specify one of the following values or an arbitrary value:
<blank>InformationStopWarning
|
| ErrorMessage | The error message displayed when validation fails. |
| ErrorTitle | The error title displayed when validation fails. |
A valid entry must contain the specified number of characters.
| Argument | Description |
|---|---|
| SpecifySheetBy |
Select how to specify which worksheet to select while the method is being executed.
Specify one of the following values or an arbitrary value:
<blank>SheetNameSheetNumberAllSheets
|
| SheetSpec | Specify the sheet-name or sheet-number, depending on SpecifySheetBy. If SpecifySheetBy is AllSheets then SheetSpec can be left empty. |
| Address | The address of a range to select for the execution of the method. |
| CellByCell | Whether to execute the method on a cell-by-cell basis, or on the range specified by the address. |
| ReverseOrder | Whether to loop through the rows and columns from highest to lowest. Only applies when CellByCell is true. |
| MethodCondition |
If the result of method-condition expression resolves to FALSE, then the method is not executed.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| CellCondition |
If the result of the condition expression resolves to FALSE, then the current cell is skipped.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| Operator |
The operator to be used for the validation.
Specify one of the following values or an arbitrary value:
<blank>BetweenNot BetweenEqualNot EqualGreater thanGreater than or EqualLess thanLess than or Equal
|
| Length | The Length specification. |
| Minimum | The minimum value. |
| Maximum | The maximum value. |
| IgnoreBlank |
Specifies whether validation should be skipped if either validation formula refers to a blank cell.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| ShowInputMessage |
Specifies whether an input message should be displayed when one of the represented cells is active.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| InputMessage | The input message displayed when one of the represented cells is active. |
| InputTitle | The input title displayed when one of the represented cells is active. |
| ShowError |
Specifies whether errors should be shown when validation fails.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| AlertStyle |
The alert style used when validation fails.
Specify one of the following values or an arbitrary value:
<blank>InformationStopWarning
|
| ErrorMessage | The error message displayed when validation fails. |
| ErrorTitle | The error title displayed when validation fails. |
The entered value must be a valid time.
| Argument | Description |
|---|---|
| SpecifySheetBy |
Select how to specify which worksheet to select while the method is being executed.
Specify one of the following values or an arbitrary value:
<blank>SheetNameSheetNumberAllSheets
|
| SheetSpec | Specify the sheet-name or sheet-number, depending on SpecifySheetBy. If SpecifySheetBy is AllSheets then SheetSpec can be left empty. |
| Address | The address of a range to select for the execution of the method. |
| CellByCell | Whether to execute the method on a cell-by-cell basis, or on the range specified by the address. |
| ReverseOrder | Whether to loop through the rows and columns from highest to lowest. Only applies when CellByCell is true. |
| MethodCondition |
If the result of method-condition expression resolves to FALSE, then the method is not executed.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| CellCondition |
If the result of the condition expression resolves to FALSE, then the current cell is skipped.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| Operator |
The operator to be used for the validation.
Specify one of the following values or an arbitrary value:
<blank>BetweenNot BetweenEqualNot EqualGreater thanGreater than or EqualLess thanLess than or Equal
|
| Minimum | The minimum value. |
| Maximum | The maximum value. |
| IgnoreBlank |
Specifies whether validation should be skipped if either validation formula refers to a blank cell.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| ShowInputMessage |
Specifies whether an input message should be displayed when one of the represented cells is active.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| InputMessage | The input message displayed when one of the represented cells is active. |
| InputTitle | The input title displayed when one of the represented cells is active. |
| ShowError |
Specifies whether errors should be shown when validation fails.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| AlertStyle |
The alert style used when validation fails.
Specify one of the following values or an arbitrary value:
<blank>InformationStopWarning
|
| ErrorMessage | The error message displayed when validation fails. |
| ErrorTitle | The error title displayed when validation fails. |
The entered value must be a whole number.
| Argument | Description |
|---|---|
| SpecifySheetBy |
Select how to specify which worksheet to select while the method is being executed.
Specify one of the following values or an arbitrary value:
<blank>SheetNameSheetNumberAllSheets
|
| SheetSpec | Specify the sheet-name or sheet-number, depending on SpecifySheetBy. If SpecifySheetBy is AllSheets then SheetSpec can be left empty. |
| Address | The address of a range to select for the execution of the method. |
| CellByCell | Whether to execute the method on a cell-by-cell basis, or on the range specified by the address. |
| ReverseOrder | Whether to loop through the rows and columns from highest to lowest. Only applies when CellByCell is true. |
| MethodCondition |
If the result of method-condition expression resolves to FALSE, then the method is not executed.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| CellCondition |
If the result of the condition expression resolves to FALSE, then the current cell is skipped.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| Operator |
The operator to be used for the validation.
Specify one of the following values or an arbitrary value:
<blank>BetweenNot BetweenEqualNot EqualGreater thanGreater than or EqualLess thanLess than or Equal
|
| Minimum | The minimum value. |
| Maximum | The maximum value. |
| IgnoreBlank |
Specifies whether validation should be skipped if either validation formula refers to a blank cell.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| ShowInputMessage |
Specifies whether an input message should be displayed when one of the represented cells is active.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| InputMessage | The input message displayed when one of the represented cells is active. |
| InputTitle | The input title displayed when one of the represented cells is active. |
| ShowError |
Specifies whether errors should be shown when validation fails.
Specify one of the following values or an arbitrary value:
<blank>FALSETRUE
|
| AlertStyle |
The alert style used when validation fails.
Specify one of the following values or an arbitrary value:
<blank>InformationStopWarning
|
| ErrorMessage | The error message displayed when validation fails. |
| ErrorTitle | The error title displayed when validation fails. |