Link to home
Start Free TrialLog in
Avatar of flfmmqp
flfmmqp

asked on

Excel Data Validation via VBA

I'm using access to create some default templates for various locations.  I am trying to create a data validation rule for a group of cells.  However, I continue to get an error on the .add Type line .  



'Create Data Validation rule for Stores rows.
    objExcel.Range("B9:B108,O9:O108").Select

    With objExcel.Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=AH$1:AH$3"
       .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = False
    End With
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi, flfmmqp.

If it's not simply a paste error, then...
  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=AH$1:AH$3"        .IgnoreBlank = True

Open in new window

...should be...
  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=AH$1:AH$3"
  .IgnoreBlank = True

Open in new window

Regards,
Brian.
Avatar of flfmmqp
flfmmqp

ASKER

Thanks.  Both answer were correct but I am giving the points to the fastest responder.  Thanks for the help.