Class Validation (Excel VBA)

The class Validation represents data validation for a worksheet range.

The main procedures of class Validation are Add and Delete

Themes

Some procedures in this class have been grouped together in themes and are described on separate theme pages

Name with its procedures ErrorTitle and InputTitle
Formula with its procedures Formula1 and Formula2
Display/Show with its procedures ShowError and ShowInput

Methods

These are the main methods of the Validation class

Add - Adds data validation to the specified range.

With Range("e5").Validation 
 .Add Type:=xlValidateWholeNumber, _ 
 AlertStyle:= xlValidAlertStop, _ 
 Operator:=xlBetween, Formula1:="5", Formula2:="10" 
 .InputTitle = "Integers" 
 .ErrorTitle = "Integers" 
 .InputMessage = "Enter an integer from five to ten" 
 .ErrorMessage = "You must enter a number from five to ten" 
End With

Delete - Deletes the object.

Other Methods

Modify - Modifies data validation for a range.

Range("e5").Validation _ 
 .Modify xlValidateList, xlValidAlertStop, _ 
 xlBetween, "=$A$1:$A$10"

Properties

AlertStyle returns the validation alert style.

MsgBox Range("e5").Validation.AlertStyle

ErrorMessage returns or sets the data validation error message.

With Range("e5").Validation 
 .Add Type:=xlValidateWholeNumber, _ 
 AlertStyle:= xlValidAlertStop, _ 
 Operator:=xlBetween, Formula1:="5", Formula2:="10" 
 .InputTitle = "Integers" 
 .ErrorTitle = "Integers" 
 .InputMessage = "Enter an integer from five to ten" 
 .ErrorMessage = "You must enter a number from five to ten" 
End With

IgnoreBlank true if blank values are permitted by the range data validation.

Range("e5").Validation.IgnoreBlank = True

IMEMode returns or sets the description of the Japanese input rules. Can be one of the XlIMEMode constants listed in the following table.

With Range("E5").Validation 
    .Add Type:=xlValidateWholeNumber, _ 
        AlertStyle:= xlValidAlertStop, _ 
        Operator:=xlBetween, Formula1:="5", Formula2:="10" 
    .InputTitle = "???" 
    .ErrorTitle = "???" 
    .InputMessage = "5??10?????????????" 
    .ErrorMessage = "???????5??10???????" 
    .IMEMode = xlIMEModeAlpha 
End With

InCellDropdown true if data validation displays a drop-down list that contains acceptable values.

With Range("e5").Validation 
 .Add xlValidateList, xlValidAlertStop, xlBetween, "=$A$1:$A$10" 
 .InCellDropdown = True 
End With

InputMessage returns or sets the data validation input message.

With Range("e5").Validation 
 .Add Type:=xlValidateWholeNumber, _ 
 AlertStyle:= xlValidAlertStop, _ 
 Operator:=xlBetween, Formula1:="5", Formula2:="10" 
 .InputTitle = "Integers" 
 .ErrorTitle = "Integers" 
 .InputMessage = "Enter an integer from five to ten" 
 .ErrorMessage = "You must enter a number from five to ten" 
End With

Operator returns a Long value that represents the operator for the data validation. See XlFormatConditionOperator.

Parent returns the parent object for the specified object. Read-only.

Type returns a Long value containing an XlDVType constant that represents the data type validation for a range.

Value returns a Boolean value that indicates if all the validation criteria are met (that is, if the range contains valid data).