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:
Settings
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 | Description | Dialog |
|---|---|---|
List (DataValidationType.List) | 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) Decimal (DataValidationType.Decimal) | Restricts numbers outside a specified range. |
|
Date (DataValidationType.Date) Time (DataValidationType.Time) | Restricts dates and times outside a certain time frame. |
|
Text Length (DataValidationType.TextLength) | Limits the number of text characters. |
|
Custom (DataValidationType.Custom) | 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. |
|
Input Message
Options under this tab allow end-users to create a message that explains what data can be entered in a cell.
You can type the message title (DataValidation.InputTitle) in the Title box and text (DataValidation.InputMessage) in the Input Message box.
As a result, a yellow text box appears near the cell displaying the input message.
Error Alert
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.
Tip
Use the DataValidationCollection.Add method to create a new validation rule in code. Refer to the Data Validation topic for details.