XL.Sheet.AddDataValidation
--------------------------

Adds a data validation for the specified range.

| Component | Version | macOS | Windows | Linux | Server | iOS SDK |
|---|---|---|---|---|---|---|
| [XL](component_XL.md) | [10.5](newinversion105.md) | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |

MBS( "XL.Sheet.AddDataValidation"; BookRef; SheetIndex; Type; Operator; rowFirst; rowLast; ColFirst; ColLast; Value1; Value2 { ; allowBlank; hideDropDown; showInputMessage; showErrorMessage; promptTitle; Prompt; errorTitle; error; errorStyle } ) 

**MBS**( **"XL.Sheet.AddDataValidation";** /\* Adds a data validation for the specified range. \*/  
**$BookRef**; /\* The reference number for the workbook.e.g. $ref \*/   
**$SheetIndex**; /\* The index of the sheet to delete. This number ranges from 0 to [XL.Book.SheetCount](XLBookSheetCount.md)-1.e.g. 0 \*/   
**$Type**; /\* Number for the type of data validation. \*/   
**$Operator**; /\* Number for the relational operator of data validation. \*/   
**$rowFirst**; /\* The first row for the area. First row has index 0.e.g. $row \*/   
**$rowLast**; /\* The last row for the area. First row has index 0.e.g. $row \*/   
**$ColFirst**; /\* The first column for the area. First column has index 0.e.g. $column \*/   
**$ColLast**; /\* The last column for the area. First column has index 0.e.g. $column \*/   
**$Value1**; /\* The first value for relational operator, use double quotes if you want to specify a list of values directly (for example "A,B,C") and don't use quotes if you want to specify a reference to area with values (for example A1:A6); \*/   
**$Value2**; /\* The second value for VALIDATION\_OP\_BETWEEN or VALIDATION\_OP\_NOTBETWEEN operator; allowBlank - a boolean value indicating whether the data validation treats empty or blank entries as valid, 'true' means empty entries are OK and do not violate the validation constraints; \*/   
**$allowBlank**; /\* Optional; A boolean value indicating whether the data validation treats empty or blank entries as valid, 'true' means empty entries are OK and do not violate the validation constraints.  
Default is 1. \*/   
**$hideDropDown**; /\* Optional; A boolean value indicating whether to display the dropdown combo box for a list type data validation (VALIDATION\_TYPE\_LIST)  
Default is 0. \*/   
**$showInputMessage**; /\* Optional; A boolean value indicating whether to display the input prompt message  
Default is 1. \*/   
**$showErrorMessage**; /\* Optional; a boolean value indicating whether to display the error alert message when an invalid value has been entered, according to the criteria specified  
Default is 1. \*/   
**$promptTitle**; /\* Optional; Title bar text of input prompt. \*/   
**$Prompt**; /\* Optional; message text of input prompt \*/   
**$errorTitle**; /\* Optional; title bar text of error alert \*/   
**$error**; /\* Optional; message text of error alert \*/   
**$errorStyle**) /\* Optional; Number for the style of error alert used for this data validation.  
Default is 0 for stop error style. \*/ 

### Parameters

| Parameter | Description | Example | Flags |
|---|---|---|---|
| BookRef | The reference number for the workbook. | $ref |  |
| SheetIndex | The index of the sheet to delete. This number ranges from 0 to [XL.Book.SheetCount](XLBookSheetCount.md)-1. | 0 |  |
| Type | Number for the type of data validation. |  |  |
| Operator | Number for the relational operator of data validation. |  |  |
| rowFirst | The first row for the area. First row has index 0. | $row |  |
| rowLast | The last row for the area. First row has index 0. | $row |  |
| ColFirst | The first column for the area. First column has index 0. | $column |  |
| ColLast | The last column for the area. First column has index 0. | $column |  |
| Value1 | The first value for relational operator, use double quotes if you want to specify a list of values directly (for example "A,B,C") and don't use quotes if you want to specify a reference to area with values (for example A1:A6); |  |  |
| Value2 | The second value for VALIDATION\_OP\_BETWEEN or VALIDATION\_OP\_NOTBETWEEN operator; allowBlank - a boolean value indicating whether the data validation treats empty or blank entries as valid, 'true' means empty entries are OK and do not violate the validation constraints; |  |  |
| allowBlank | A boolean value indicating whether the data validation treats empty or blank entries as valid, 'true' means empty entries are OK and do not violate the validation constraints.   Default is 1. |  | Optional |
| hideDropDown | A boolean value indicating whether to display the dropdown combo box for a list type data validation (VALIDATION\_TYPE\_LIST)   Default is 0. |  | Optional |
| showInputMessage | A boolean value indicating whether to display the input prompt message   Default is 1. |  | Optional |
| showErrorMessage | a boolean value indicating whether to display the error alert message when an invalid value has been entered, according to the criteria specified   Default is 1. |  | Optional |
| promptTitle | Title bar text of input prompt. |  | Optional |
| Prompt | message text of input prompt |  | Optional |
| errorTitle | title bar text of error alert |  | Optional |
| error | message text of error alert |  | Optional |
| errorStyle | Number for the style of error alert used for this data validation.   Default is 0 for stop error style. |  | Optional |

### Result

Returns OK or error.

### Description

Adds a data validation for the specified range.  
Only for xlsx files.  
  
If value1 and value2 are numbers, we call xlSheetAddDataValidationDoubleW/xlSheetAddDataValidationDoubleExW in LibXL for double/date comparison. Otherwise we call xlSheetAddDataValidationW/xlSheetAddDataValidationExW with text. If you pass more than 12 parameters, we call the Ex variants of the functions and pass those extra parameters.  
  
| DataValidationType | value | Description |
|---|---|---|
| VALIDATION\_TYPE\_NONE | 0 | No data validation. |
| VALIDATION\_TYPE\_WHOLE | 1 | Data validation which checks for whole number values satisfying the given condition. |
| VALIDATION\_TYPE\_DECIMAL | 2 | Data validation which checks for decimal values satisfying the given condition. |
| VALIDATION\_TYPE\_LIST | 3 | Data validation which checks for a value matching one of list of values. |
| VALIDATION\_TYPE\_DATE | 4 | Data validation which checks for date values satisfying the given condition. |
| VALIDATION\_TYPE\_TIME | 5 | Data validation which checks for time values satisfying the given condition. |
| VALIDATION\_TYPE\_TEXTLENGTH | 6 | Data validation which checks for text values, whose length satisfies the given condition. |
| VALIDATION\_TYPE\_CUSTOM | 7 | Data validation which uses a custom formula to check the cell value. |

  
| DataValidationOperator | Value | Description |
|---|---|---|
| VALIDATION\_OP\_BETWEEN | 0 | Data validation which checks if a value is between two other values. |
| VALIDATION\_OP\_NOTBETWEEN | 1 | Data validation which checks if a value is not between two other values. |
| VALIDATION\_OP\_EQUAL | 2 | Data validation which checks if a value is equal to a specified value. |
| VALIDATION\_OP\_NOTEQUAL | 3 | Data validation which checks if a value is not equal to a specified value. |
| VALIDATION\_OP\_LESSTHAN | 4 | Data validation which checks if a value is less than a specified value. |
| VALIDATION\_OP\_LESSTHANOREQUAL | 5 | Data validation which checks if a value is less than or equal to a specified value. |
| VALIDATION\_OP\_GREATERTHAN | 6 | Data validation which checks if a value is greater than a specified value. |
| VALIDATION\_OP\_GREATERTHANOREQUAL | 7 | Data validation which checks if a value is greater than or equal to a specified value. |

  
| DataValidationErrorStyle | value | Description |
|---|---|---|
| VALIDATION\_ERRSTYLE\_STOP | 0 | This data validation error style uses a stop icon in the error alert. |
| VALIDATION\_ERRSTYLE\_WARNING | 1 | This data validation error style uses a warning icon in the error alert. |
| VALIDATION\_ERRSTYLE\_INFORMATION | 2 | This data validation error style uses an information icon in the error alert. |

### Examples

Add a data validation and query it:

 # Create XLSX in file XL Test   
  
If \[ MBS ("[XL.IsInitialized](XLIsInitialized.md)") ≠ 1 \]  
 Perform Script \[ “InitXL” ; Specified: From list ; Parameter: \]  
End If  
\#   
\# create new xlsx file   
Set Variable \[ $book ; Value: MBS ( "[XL.NewBook](XLNewBook.md)"; 1 ) \]  
Set Variable \[ $sheet ; Value: MBS ( "[XL.Book.AddSheet](XLBookAddSheet.md)"; $book ; "Sales Receipt") \]  
Set Variable \[ $r ; Value: MBS ( "XL.Sheet.AddDataValidation"; $book ; $sheet ; 2 /\* decimal \*/; 0 /\* between \*/; 1; 2; 3; 4; "1"; "3" /\* between 1 and 3 \*/; True /\* allow empty \*/ ) \]  
Set Variable \[ $$ValidationCount ; Value: MBS ( "[XL.Sheet.DataValidationSize](XLSheetDataValidationSize.md)"; $book ; $sheet ) \]  
Set Variable \[ $$ValidationJSON ; Value: MBS ( "[XL.Sheet.DataValidation](XLSheetDataValidation.md)"; $book ; $sheet ) \]  
\# inspect variables in debugger   
Set Variable \[ $r ; Value: MBS ("[XL.Book.Release](XLBookRelease.md)"; $book ) \]  
<small>  
Example result:   
{ "sheet" : 0 , "index" : 0 , "type" : 2 , "op" : 0 , "rowFirst" : 1 , "rowLast" : 2 , "colFirst" : 3 , "colLast" : 4 , "value1" : "1" , "value2" : "3" , "allowBlank" : true, "hideDropDown" : false, "showInputMessage" : true, "showErrorMessage" : true, "promptTitle" : "" , "prompt" : "" , "errorTitle" : "" , "error" : "" , "errorStyle" : 0 } </small>### See also

- [XL.Book.AddSheet](XLBookAddSheet.md)
- [XL.Book.Release](XLBookRelease.md)
- [XL.Book.SheetCount](XLBookSheetCount.md)
- [XL.IsInitialized](XLIsInitialized.md)
- [XL.NewBook](XLNewBook.md)
- [XL.Sheet.DataValidation](XLSheetDataValidation.md)
- [XL.Sheet.DataValidations](XLSheetDataValidations.md)
- [XL.Sheet.DataValidationSize](XLSheetDataValidationSize.md)
- [XL.Sheet.RemoveDataValidations](XLSheetRemoveDataValidations.md)

### Release notes

- **Version 10.5**
    - Added [XL.Sheet.AddDataValidation](https://www.mbsplugins.eu/XLSheetAddDataValidation.shtml) and [XL.Sheet.RemoveDataValidations](https://www.mbsplugins.eu/XLSheetRemoveDataValidations.shtml) functions.

### Blog Entries

- [New in MBS FileMaker Plugin 10.5](https://www.mbsplugins.de/archive/2020-11-23/New_in_MBS_FileMaker_Plugin_10/monkeybreadsoftware_blog_filemaker)
- [Neues MBS FileMaker Plugin 10.5 - Über 6400 Funktionen in einem Plugin](https://www.mbsplugins.de/archive/2020-11-17/Neues_MBS_FileMaker_Plugin_105/monkeybreadsoftware_blog_filemaker)
- [MBS FileMaker Plugin 10.5 - More than 6400 Functions In One Plugin](https://www.mbsplugins.de/archive/2020-11-17/MBS_FileMaker_Plugin_105_-_Mor/monkeybreadsoftware_blog_filemaker)
- [MBS FileMaker Plugin, version 10.5pr7](https://www.mbsplugins.de/archive/2020-11-02/MBS_FileMaker_Plugin_version_1/monkeybreadsoftware_blog_filemaker)

### FileMaker Magazin

- [Ausgabe 6/2020, Seite 24](https://filemaker-magazin.de/neuigkeit/4118-Appetithappen-FMM_202006)

This function checks for a license.

Created 30th October 2020 , last changed 21st May 2026

  
[XL.Sheet.AddConditionalFormatting](XLSheetAddConditionalFormatting.md) - [XL.Sheet.AddHyperlink](XLSheetAddHyperlink.md)

[HTML Version](XLSheetAddDataValidation.shtml)