VBA Data Validation Error

[toboggan]

Hello All,

This is my first post, so forgive me if my post is somewhat inept.

I'm having trouble with some VBA code. I've got some cells that I need to create a drop down list based on the selection in another drop down list. I can do this with no trouble via excel, but trying to create it again via VBA is not going well. I used the macro recorder for some guidance on what to do as far as coding and it provided the following:

With Range("BQ208").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(SUBSTITUTE($BQ$207,"" "",""_""))"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = False
    End With

However, when I run the macro, I'm getting the following error: "Run-time error '1004': Application-defined or object-defined error"

I've googled the heck out of this question for the last couple days and can't seem to figure out what the problem is. When running the debugger, it's showing this line as being the issue:

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(SUBSTITUTE($BQ$207,"" "",""_""))"

Any help would be much appreciated.


Thanks!

 

[Jonmo1]

So BQ207 is blank?
That explains the error.

When doing it by hand, you get the error that it evaluates to an error?
And you can click, yes do it anyway.
Then it works.

VBA doesn't allow that option, it just stops period.
You need to put a valid named range text in BQ207 BEFORE you run the code to put that valiation in place.

 

[toboggan]

So a workaround might be to enter code to put a selection from the BQ207 list into BQ207, then apply the problematic code to BQ208, then go back and replace the selection entered in BQ207 with its data validation?

 

[toboggan]

Just tried it and that seems to fix it. Here is what I did for the code including copying and pasting the validation to cells below:

Range("BQ207") = "Associate"
    Range("BQ208").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(SUBSTITUTE($BQ$207,"" "",""_""))"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = False
    End With
    Selection.Copy
    Range("BQ209:BQ222").Select
    Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("BQ207") = ""

Associate is one of the possible selections from the list that should be in BQ207.


Thanks so much for your help!

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[bharathk03]

Hi,
I have also got the same issue in my vba code. Cannot successfully execute "=INDIRECT(SUBSTITUTE($M$4," ","_"))" through VBA. Please help if you have got a solution.

TIA.