CODEWITHSUNDEEP
excelvbalistvalidation
Shan
Shan

Reputation: 439

Data validation using formula Indirect reference

I have a following code recorded but if i run it from VBA it is throwing me an error (2nd line). You can do it manually but it gives you the warning and if you proceed with the warning then it takes the formula in Data validation.

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

E13 is initially empty , it is a user selectable list.

Upvotes: 1

Views: 1053

Answers (1)

Axel Richter
Axel Richter

Reputation: 61900

If E13 is empty, then Formula1:="=INDIRECT(E13)" results in #REF! error. That's why the runtime exception in VBA.

The error also occurs in GUI but the GUI only warns and then ignores the error. VBA does not ignoring the error.

So we need to avoid this #REF! error. To do so we can use 

Formula1:="=IF(E13="""",A100000,INDIRECT(E13))"

Now E13 can either be empty or must contain a correct reference string (address or name) which is resolvable using INDIRECT. If E13 is empty. then A100000will be the list reference. So A100000 should be a cell which is empty in every cases.

Upvotes: 1

Related Questions