VBA Validation list problem

Question

Dearest Wizards,

I have many col of names.  Using 'Define Name' I have given each set of names a unique name.  I've created a subroutine that has a cell location and one of the unique names passed to it.

the intent of the subroutine is to plant whatever validation list I pass to it in the cell that I pass to it.  The cell select part is working for me but I can't get the proper code for the range name.  I did a 'Record Macro' to look at the format and it gave me this: 

    With Selection.Validation

        .Delete

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

        xlBetween, Formula1:="=ValListNameA"

        .IgnoreBlank = True

        .InCellDropdown = True

        .InputTitle = ""

        .ErrorTitle = ""

        .InputMessage = ""

        .ErrorMessage = ""

        .ShowInput = True

        .ShowError = True

    End With

I'm carrying the name of the list to the subroutine with the variable LIST_NAME so I tried:

       .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

        xlBetween, Formula1:="=" & LIST_NAME & """

but I get an error on the run AND VBA keeps 'correcting' the """ to """"

I tried the following too but this also gets an error

       .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

        xlBetween, Formula1:="=" & LIST_NAME & Chr(34)

Any suggestions?

TIA

Sam

Answer 1

With a defined name of Name1 identifying a vertical contiguous range of cells 1 column wide suitable to being the source for a data validation dropdown, this worked for me.

Sub Test()

Dim v As String

v = "Name1"

abc v

End Sub

Sub abc(LIST_NAME As String)

   With Selection.Validation

        .Delete

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

        xlBetween, Formula1:="=" & LIST_NAME

        .IgnoreBlank = True

        .InCellDropdown = True

        .InputTitle = ""

        .ErrorTitle = ""

        .InputMessage = ""

        .ErrorMessage = ""

        .ShowInput = True

        .ShowError = True

    End With

End Sub

Answer 2

Hi,

To further isolate the issue, please provide the details of these following questions:

1. What is the error message you receive?
2. Where did you get the error message?
3. What have you done so far to resolve this?
4. When did this error message appeared?

We'll be waiting for your response.

Regards.