Welcome to OStack Knowledge Sharing 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 Answer

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...