How to use Named Array Constant to fill an Active-X ComboBox in Excel

I'd like to setup my ComboBox values as a named array constant, where you go into the Name Manager and d开发者_Go百科efine a name and give it a value like:

={"A", "B", "C"}

I've found that ListFillRange will only take a Range so I can't assign to that.

Though it would work for a normal named range, like so:

myComboBox.ListFillRange = "MyList"

So how do you assign a Named Array Constant to fill a ComboBox?

I can't get your method to work for me but it's probably my setup?

If it were me attempting this I would either

1. Load the list programmatically item by item

or

2i) Assume arbitrarily "J" is the listfill range

2ii) Define a named range, say "COLJ" = =Sheet1!$J$2:INDEX($J$2:$J$65000,COUNTA($J$2:$J$65000))

2ii) Copy/Filter the items you want into J2:Jx

2ii) Assign the Listfillrange using VBA, myComboBox.ListFillRange = "COLJ"

After much searching I found this solution:

myComboBox.List() = Evaluate(ThisWorkbook.Names("MyList").RefersTo)