Skip to main content

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



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.



Use the DataValidationCollection.Add method to create a new validation rule in code. Refer to the Data Validation topic for details.