excel - populate combobox in VBA with array elements

Question

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:

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.

Answer 1

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