CODEWITHSUNDEEP
excelvbanamed-ranges
Dwayne Dibbley
Dwayne Dibbley

Reputation: 355

Named range array not working or not possible?

I have a list of options that exceed the 256 chars limit for data validation, so im trying to use a named range from the string array but it does not seem to work or is not possible?

heres the code im trying:

ActiveWorkbook.Names.Add Name:="mylist", RefersTo:="={""one"",""two""}"

With Target.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=mylist"
End With

the list is generated from an external database query in vba, i have just show a simple example above.

Thanks

Upvotes: 1

Views: 208

Answers (1)

Error 1004
Error 1004

Reputation: 8220

This is how you create a named range called Test using Sheet1.Range(A1:A10) & import a data validation with range values in cell Sheet1.Range("B1")

Option Explicit


Sub test()

    With ThisWorkbook

        .Names.Add Name:="rngTEst", RefersToR1C1:=.Worksheets("Sheet1").Range("A1:A10") 'Create the range

        With .Worksheets("Sheet1").Range("B1").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=INDIRECT(""rngTest"")"
        End With

    End With

End Sub

Upvotes: 2

Related Questions