CODEWITHSUNDEEP
excelvba
Chandrasekar R
Chandrasekar R

Reputation: 123

Data Validation with Indirect formula

I have created a VBA for Data Validation with indirect formula, but throws an error while running the macro. Am I missing something?

     With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=INDIRECT(BD2)"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

Upvotes: 0

Views: 199

Answers (1)

Chronocidal
Chronocidal

Reputation: 7951

Setting an INDIRECT Validation throws an error if the INDIRECT does not evaluate to a proper range at the time you create it - if you do it manually, you get a pop-up saying "do you want to continue anyway", but in VBA it just fails. Testing with DisplayAlerts = False does not seem to fix this.

The only options I can think of are to either populate BD2 with a temporary, valid, value, or to have a "Null" range included in an IF statement inside your INDIRECT, e.g. Formula1:="=INDIRECT(IF(LEN(BD2)>0,BD2,""DropDown_Default""))", where DropDown_Default is a Named Range which contains a single blank cell.

Upvotes: 1

Related Questions