Data Validation Dialog
- 3 minutes to read
The Data Validaition dialog allows end-users to create data validation rules to prevent other users from entering incorrect values in validated cells. End-users can also set warnings displayed when entering invalid data, or create pop-up messages to explain the reason for the restriction or instruct users how to correct errors.
End-users can invoke the dialog by clicking the Data Validation item in the Data Validation drop-down menu. Add the Data Tools ribbon page group to enable this menu. Refer to the Create a Simple Spreadsheet Application tutorial to learn how to create a spreadsheet application with the ribbon UI.
The dialog includes the following tabs:
This tab allows end-users to create a validation rule. The table below describes the data validation types (DataValidation.ValidationType) available in the Allow list.
Data Validation Type
Restricts data entry to predefined items in a list. In the Source box, end-users can type a list of comma-separated values, define a name for the cell range of valid entries, or select this cell range in the worksheet by using the Collapse Dialog button. Check the In-cell dropdown box to make a drop-down list of available values appear when selecting a cell to which the validation is applied.
Whole Number (DataValidationType.WholeNumber)
Restricts numbers outside a specified range.
Restricts dates and times outside a certain time frame.
Text Length (DataValidationType.TextLength)
Limits the number of text characters.
Validates data based on formulas or values in other cells. The formula should return a logical value: TRUE for valid values and FALSE for incorrect values. The formula may also contain cell references, so that end-users can calculate the allowable values based on the content of other cells.
Options under this tab allow end-users to create a message that explains what data can be entered in a cell.
As a result, a yellow text box appears near the cell displaying the input message.
Using this tab, end-users can create a message that appears when incorrect data is entered.
Select the desired item from the Style drop-down list to create one of the following types of error messages (DataValidation.ErrorStyle):
Stop (default) - specifies that users are not allowed to enter invalid data in a validated cell.
Warning - allows users to enter an incorrect value in a validated cell, but warns them that the specified value is invalid.
Information - informs users that they entered an incorrect value and allows them to accept or reject this value.