VBA UserForm multiple comboboxes with same values

Question

I want to make 3 different comboboxes in an Excel UserForm. In total there will be x of each type.

I want to make a code like this:

With ComboboxAi for i = 1 to 5
    .AddItem "monday"
    .AddItem "tuesday"

With ComboboxBj for j = 1 to 6
    .AddItem "january"
    .AddItem "february"

My comboboxes are named like: ComboboxA1, .., ComboboxA5, ComboboxB1, .., ComboboxB6

Can someone help me make the proper code?

CallumDA · Accepted Answer · 2017-11-03 10:00:10Z

2

This is the syntax:

    Dim i As Long

    For i = 1 To 5
        With Controls("ComboboxA" & i)
            .AddItem "Monday"
            .AddItem "Tuesday"
        End With
    Next i

    For i = 1 To 6
        With Controls("ComboboxB" & i)
            .AddItem "January"
            .AddItem "February"
        End With
    Next i

However, the following could be a better way to achieve the same thing:

    Dim ctrl As MSForms.Control

    For Each ctrl In Controls
        If TypeOf ctrl Is MSForms.ComboBox Then
            If ctrl.Name Like "ComboboxA*" Then ctrl.List = Array("Monday", "Tuesday")
            If ctrl.Name Like "ComboboxB*" Then ctrl.List = Array("January", "February")
        End If
    Next ctrl

edited Nov 3, 2017 at 10:00

answered Nov 3, 2017 at 9:48

CallumDA

12.1k6 gold badges33 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA UserForm multiple comboboxes with same values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related