Excel VBA Compile Error with String Array

Question

I have a problem with VBA in Excel. My goal was to have a Userform with several comboboxes, with all the same content, which is extracted from a Excel-chart. I used the command

ComboBox.Rowsource = "A1:A10"

And that for every combobox i had in my userform. then i realized it was much code, because i wrote this command for every box again. So i tried out to use a string array, lets call it "A", with the content "Combobox1" and "Combobox2" and so on. But when i tried to use this in the code -

for i = 1 to 10

A(i-1).rowsource = "A1:A10"

next

• i got the warning: Compile Error: Invalide qualifier

What is my mistake?

Many Thanks for any help, D.K.

Answer 1

You can use the List property of the ComboBox to fill your combo-box with values from a Range (column):

ComboBox.List = Range("A1:A10").Value

To populate a ComboBox with values from A array, you can use the code below:

Dim A() As Variant

A = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) '<-- populate array (for example)
ComboBox.List = A '<-- populate combo-box with array values

---

To populate A array with values from a Range, and later on populate your ComboBox from that A array, use the code below:

Dim A() As Variant

A = Application.Transpose(Range("A1:A10"))  '<-- populate array from Range("A1:A10")
ComboBox.List = A '<-- populate combo-box with array values

Answer 2

Instead of string array you could use VBA.Collection to store the comboboxes of the Form. This might be more easy then array.

Dim i As Integer
Dim A As Collection
Set A = New VBA.Collection

' One way of adding specific comboboxes to the collection
A.Add Me.ComboBox1, Me.ComboBox1.Name
A.Add Me.ComboBox2, Me.ComboBox2.Name
A.Add Me.ComboBox3, Me.ComboBox3.Name
' and so on for all combo boxes on the form

Set A = New VBA.Collection

' Another way would be to loop through the controls and filter the comboboxes out
Dim c As MSForms.control
For Each c In Me.Controls
    If TypeName(c) = "ComboBox" Then
        A.Add c, c.Name
    End If
Next c

For i = 1 To A.Count
    A(i).RowSource = "A1:A10"
Next