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
- 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)
精彩评论