Multi ComboBox

[McWaterMan]

Ok. So I have a userform I'm putting together that contains 20 combo boxes that will all refer to the same named range in my workbook. I was able to make it work but its pretty ugly. Any tips for a better way?

For Each MainCode In [MainCodeList]
Me.cmbMain1.AddItem MainCode
Me.cmbMain2.AddItem MainCode
Me.cmbMain3.AddItem MainCode
Me.cmbMain4.AddItem MainCode
Me.cmbMain5.AddItem MainCode
Me.cmbMain6.AddItem MainCode
Me.cmbMain7.AddItem MainCode
Me.cmbMain8.AddItem MainCode
Me.cmbMain9.AddItem MainCode
Me.cmbMain10.AddItem MainCode
Me.cmbMain11.AddItem MainCode
Me.cmbMain12.AddItem MainCode
Me.cmbMain13.AddItem MainCode
Me.cmbMain14.AddItem MainCode
Me.cmbMain15.AddItem MainCode
Me.cmbMain16.AddItem MainCode
Me.cmbMain17.AddItem MainCode
Me.cmbMain18.AddItem MainCode
Me.cmbMain19.AddItem MainCode
Me.cmbMain20.AddItem MainCode
Next MainCode

[.paul.]

That looks like VBA, right?
It should be in the Office Development Forum

[vbfbryce]

You can do something like this (substitute for the correct range in your case):

Private Sub UserForm_Click()
    Dim cmb As ComboBox
    
    For Each cmb In Me.Controls
        cmb.List = Worksheets("Sheet1").Range("a1:a5").Value
    Next
End Sub

[westconn1]

@ bryce did you test?
probably better to use the rowsource
also if there are any other controls (textbox or whatever) a type mismatch will occur

[vbfbryce]

Yes, I tested, but to your point, without any other controls. Thanks for correcting!

[vbfbryce]

When I added other controls to the form, this worked:

Private Sub UserForm_Click()
    Dim ctrl As Control
    
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is MSForms.ComboBox Then
            ctrl.List = Worksheets("Sheet1").Range("a1:a5").Value
        End If
    Next
End Sub

Or, using row source, sub in this line:

ctrl.RowSource = "Sheet1!a1:a5"

[Elroy]

McWaterMan,

This is aircode (i.e., no testing), but you could also do something like the following. Also, this thread inspired me to mock up a framework for VBA control arrays, found here. However, that might be more than you'd like to tackle right now. But the following does make a loop out of what you're doing.

For Each MainCode In [MainCodeList]
    Dim ctl As Control
    For Each ctl In Me.Controls
        If TypeName(ctl) = "ComboBox" Then
            If Left$(ctl.Name, 7) = "cmbMain" Then
                ctl.AddItem MainCode
            End If
        End If
    Next
Next

Enjoy,
Elroy