开发者

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)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜