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.

DataValidationTypevalueDescription
VALIDATION_TYPE_NONE0No data validation.
VALIDATION_TYPE_WHOLE1Data validation which checks for whole number values satisfying the given condition.
VALIDATION_TYPE_DECIMAL2Data validation which checks for decimal values satisfying the given condition.
VALIDATION_TYPE_LIST3Data validation which checks for a value matching one of list of values.
VALIDATION_TYPE_DATE4Data validation which checks for date values satisfying the given condition.
VALIDATION_TYPE_TIME5Data validation which checks for time values satisfying the given condition.
VALIDATION_TYPE_TEXTLENGTH6Data validation which checks for text values, whose length satisfies the given condition.
VALIDATION_TYPE_CUSTOM7Data validation which uses a custom formula to check the cell value.

DataValidationOperatorValueDescription
VALIDATION_OP_BETWEEN0Data validation which checks if a value is between two other values.
VALIDATION_OP_NOTBETWEEN1Data validation which checks if a value is not between two other values.
VALIDATION_OP_EQUAL2Data validation which checks if a value is equal to a specified value.
VALIDATION_OP_NOTEQUAL3Data validation which checks if a value is not equal to a specified value.
VALIDATION_OP_LESSTHAN4Data validation which checks if a value is less than a specified value.
VALIDATION_OP_LESSTHANOREQUAL5Data validation which checks if a value is less than or equal to a specified value.
VALIDATION_OP_GREATERTHAN6Data validation which checks if a value is greater than a specified value.
VALIDATION_OP_GREATERTHANOREQUAL7Data validation which checks if a value is greater than or equal to a specified value.

DataValidationErrorStylevalueDescription
VALIDATION_ERRSTYLE_STOP0This data validation error style uses a stop icon in the error alert.
VALIDATION_ERRSTYLE_WARNING1This data validation error style uses a warning icon in the error alert.
VALIDATION_ERRSTYLE_INFORMATION2This data validation error style uses an information icon in the error alert.

See also

Release notes

Blog Entries

This function checks for a license.

Created 30th October 2020, last changed 30th October 2020


XL.Sheet.AddConditionalFormatting - XL.Sheet.AddHyperlink