Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

excel - populate combobox in VBA with array elements

I have a VBA procedure (in Excel 2007) where I aspire to set the ListFillRange property of a combobox styled as a list using an array.

I know this works if I right click the combobox and write "Sheet1!$F2:$F17" next to the "ListFillRange" property. I can also do this in code. However, I am interested in dynamically setting the value of this property by assigning it an array.

I know for sure the array works as I tested it; there is probably a syntax error here:

ThisWorkbook.Worksheets("Sheet1").OLEObjects("cmbS").ListFillRange = ar

when I do this I get: "Type mismatch" error.

The result of this action should be that the component is populated with the array elements, from element(0) ... to the last element (n-1) of the array. Any pointers, thank you very much!

I also tried:

ThisWorkbook.Worksheets("Sheet1").cmbS.list = ar

and it says "permission denied"

Here are the combobox properties in case it helps: enter image description here

After testing and trying, I found this works:

ThisWorkbook.Worksheets("Sheet1").cmbS.ListFillRange = ""

Dim i As Integer
For i = LBound(ar) To UBound(ar)
    ThisWorkbook.Worksheets("Sheet1").cmbS.AddItem (ar(i))

Next

However, I am interested in populating with all values at once for faster effect, not just adding element by element.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

I know its late but maybe it is going to help someone else. At least the following code works (much faster than element for element) for me.

dim arr() as variant

arr = Worksheets("Total").Range("C2:"&lrow).Value
Worksheets("Menu").ComboBox2.List = arr

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...