Use the Add method of the DataValidation property of the Range object.
expression.Validation.Add(Type, AlertStyle, Operator,Formula1, Formula2)
Use the Type parameter to specify type of validation
Name | Value |
---|---|
xlValidateCustom | 7 |
xlValidateDate | 4 |
xlValidateDecimal | 2 |
xlValidateInputOnly | 0 |
xlValidateList | 3 |
xlValidateTextLength | 6 |
xlValidateTime | 5 |
xlValidateWholeNumber | 1 |
Use the AlertStyle parameter to specify the style of the alert warning dialog.
Name | Value |
---|---|
xlValidAlertInformation | 3 |
xlValidAlertStop | 1 |
xlValidAlertWarning | 2 |
Use the Operator parameter together with the Formula parameters to specify a rule.
Name | Value |
---|---|
xlBetween | 1 |
xlEqual | 3 |
xlGreater | 5 |
xlGreaterEqual | 7 |
xlLess | 6 |
xlLessEqual | 8 |
xlNotBetween | 2 |
xlNotEqual | 4 |
Specify the error title and error message of the alert dialog in the DataValidation property itself (see code below).
This code below relates to the table shown above. Column A’s validation allow dates, but not in the past. Columns C and D have been given drop down lists. Each list has a named source – Properties and Issue_Type.
With Range("A2:A10").Validation .Add Type:=xlValidateDate, Operator:=xlGreaterEqual, Formula1:=Date, _ AlertStyle:=xlValidAlertStop .ErrorTitle = "ReportingDate" .ErrorMessage = "You cannot enter a reporting date as a date in the past" End With With Range("C2:C10").Validation .Add Type:=xlValidateList, Operator:=xlBetween, Formula1:="=Properties", _ AlertStyle:=xlValidAlertStop .ErrorTitle = "Property" .ErrorMessage = "Please select a property from the list" End With With Range("D2:D10").Validation .Add Type:=xlValidateList, Operator:=xlBetween, Formula1:="=Issue_Type", _ AlertStyle:=xlValidAlertStop .ErrorTitle = "IssueType" .ErrorMessage = "Please select an Issue Type from the list" End With