Monkeybread Software - MBS FileMaker Plugin: XL.Sheet.AddDataValidation

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 number for the workbook. $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

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-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") ≠ 1 ]
Perform Script [ “InitXL” ; Specified: From list ; Parameter: ]
End If
#
# create new xlsx file
Set Variable [ $book ; Value: MBS( "XL.NewBook"; 1 ) ]
Set Variable [ $sheet ; Value: MBS( "XL.Book.AddSheet"; $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"; $book; $sheet ) ]
Set Variable [ $$ValidationJSON ; Value: MBS( "XL.Sheet.DataValidation"; $book; $sheet ) ]
# inspect variables in debugger
Set Variable [ $r ; Value: MBS("XL.Book.Release"; $book) ]
Add a data validation and query it:

<?xml version="1.0" encoding="UTF-8"?>
<fmxmlsnippet type="FMObjectList">
  <Step enable="True" id="89" name="# (comment)">
    <Text> Create XLSX in file XL Test</Text>
  </Step>
  <Step enable="True" id="68" name="If">
    <Restore state="False"/>
    <Calculation> MBS("XL.IsInitialized")  ≠ 1 </Calculation>
  </Step>
  <Step enable="True" id="1" name="Perform Script">
    <Calculation>   </Calculation>
    <Script id="99999" name="InitXL"/>
  </Step>
  <Step enable="True" id="70" name="End If"/>
  <Step enable="True" id="89" name="# (comment)"/>
  <Step enable="True" id="89" name="# (comment)">
    <Text> create new xlsx file</Text>
  </Step>
  <Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS( "XL.NewBook"; 1 ) </Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$book</Name>
  </Step>
  <Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS( "XL.Book.AddSheet"; $book; "Sales Receipt") </Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$sheet</Name>
  </Step>
  <Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS( "XL.Sheet.AddDataValidation"; $book; $sheet; 2 /* decimal */; 0 /* between */; 1; 2; 3; 4; "1"; "3" /* between 1 and 3 */; True /* allow empty */ ) </Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$r</Name>
  </Step>
  <Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS( "XL.Sheet.DataValidationSize"; $book; $sheet ) </Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$$ValidationCount</Name>
  </Step>
  <Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS( "XL.Sheet.DataValidation"; $book; $sheet ) </Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$$ValidationJSON</Name>
  </Step>
  <Step enable="True" id="89" name="# (comment)">
    <Text> inspect variables in debugger</Text>
  </Step>
  <Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS("XL.Book.Release"; $book) </Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$r</Name>
  </Step>
</fmxmlsnippet>

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 }

Release notes

Version 10.5
- Added XL.Sheet.AddDataValidation and XL.Sheet.RemoveDataValidations functions.

Blog Entries

FileMaker Magazin

Ausgabe 6/2020, Seite 24

This function checks for a license.

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

XL.Sheet.AddConditionalFormatting - XL.Sheet.AddHyperlink

💬 Ask a question or report a problem