MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to use the Validation... function from VBA?

Posted by Jon Hallmark on February 20, 2001 1:02 PM

Is it possible to set properties for the spreadsheet function "Validation..." from VBA? We have a need to create validations from macros.

Thanks, Jon

Posted by David Hawley on February 21, 2001 1:59 AM

Hi Jon, yes it is. Here is an example

With Range("A1").Validation
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="20"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Number"
.ErrorTitle = "Number"
.InputMessage = "Number between 1 and 20 only."
.ErrorMessage = "Sorry, must be between one and twenty"
.ShowInput = True
.ShowError = True
End With

OzGrid Business Applications