| Components | All | New | MacOS | Windows | Linux | iOS | ||||
| Examples | Mac & Win | Server | Client | Guides | Statistic | FMM | Blog | Deprecated | Old | |
XL.Sheet.AddDataValidation
Adds a data validation for the specified range.
| Component | Version | macOS | Windows | Linux | Server | iOS SDK |
| XL | 10.5 | ✅ 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 } ) More
Parameters
| Parameter | Description | Example | Flags |
|---|---|---|---|
| bookRef | The reference to the workbook. Please use XL.LoadBook to load a file. | $ref | |
| SheetIndex | The index of the sheet to delete. This number ranges from 0 to XL.Book.SheetCount-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. |
See also
Release notes
- Version 10.5
- Added XL.Sheet.AddDataValidation and XL.Sheet.RemoveDataValidations functions.
Blog Entries
- New in MBS FileMaker Plugin 10.5
- Neues MBS FileMaker Plugin 10.5 - Über 6400 Funktionen in einem Plugin
- MBS FileMaker Plugin 10.5 - More than 6400 Functions In One Plugin
- MBS FileMaker Plugin, version 10.5pr7
FileMaker Magazin
This function checks for a license.
Created 30th October 2020, last changed 30th October 2020
