[RESOLVED] [Excel - VBA] How to add the same items to multiple combo boxes?

[aranan]

Hi everyone,

I'm new to VBA-Excel and was wondering if you would happen to know how to add the same items to multiple combo boxes.

I want to set up 31 combo boxes that will contain the same items, without having to do it manually.

So far this is what my code looks like:

    With Worksheets("January")
    
    .ComboBox1.Clear
    .ComboBox1.AddItem "Regular"
    .ComboBox1.AddItem "Vacation"
    .ComboBox1.AddItem "Statutory"
    .ComboBox1.AddItem "CT - Half"
    .ComboBox1.AddItem "CT - Full"
    .ComboBox1.Text = .ComboBox1.List(0)
    
    .ComboBox2.Clear
    .ComboBox2.AddItem "Regular"
    .ComboBox2.AddItem "Vacation"
    .ComboBox2.AddItem "Statutory"
    .ComboBox2.AddItem "CT - Half"
    .ComboBox2.AddItem "CT - Full"
    .ComboBox2.Text = .ComboBox2.List(0)
    
    .ComboBox3.Clear
    .ComboBox3.AddItem "Regular"
    .ComboBox3.AddItem "Vacation"
    .ComboBox3.AddItem "Statutory"
    .ComboBox3.AddItem "CT - Half"
    .ComboBox3.AddItem "CT - Full"
    .ComboBox3.Text = .ComboBox3.List(0)

End With

As you can see it's very repetitive and time consuming.
Any help would be greatly appreciated.

[Hack]

Welcome to the forums

It is still going to be repetitive, but perhaps not quite so much.

Put all that code in a sub routine and call it for each of your combo boxes.

Public Sub LoadCombos(pcb As Combobox)
With pcb
    .Clear
    .AddItem "Regular"
    .AddItem "Vacation"
    .AddItem "Statutory"
    .AddItem "CT - Half"
    .AddItem "CT - Full"
    .Text = .List(0)
End With
End Sub

Private Sub CommandButton1_Click()
LoadCombos CombBox1
LoadCombos CombBox2
LoadCombos CombBox3
etc
End Sub

If you are using a UserForm, perhaps something like this would be useful

Dim ctrl As Control
     For Each ctrl In Me.Controls
       If TypeName(ctrl) = "Combobox" Then
           LoadCombos ctrl.Name
       End If
    Next

[westconn1]

if the comboboxes are on a worksheet you can loop through the shapes or oleobjects collection of the sheet

for i = 1 to 31
  me.shapes("combobox" & i).oleformat.object.object.additem "test"
next

if the code is in a module you need to specify the sheet by name or index, instead of using the ME keyword

[aranan]

if the comboboxes are on a worksheet you can loop through the shapes or oleobjects collection of the sheet

for i = 1 to 31
  me.shapes("combobox" & i).oleformat.object.object.additem "test"
next

if the code is in a module you need to specify the sheet by name or index, instead of using the ME keyword

This is EXACTLY what i needed!

Great stuff and thank you both for all your help

[frigidtorch]

Dim ctrl As Control
     For Each ctrl In Me.Controls
       If TypeName(ctrl) = "Combobox" Then
           LoadCombos ctrl.Name
       End If
    Next

Newbie here too..tried this code but i get an error "Type Mismatch".

How do I resolve this?

[si_the_geek]

Welcome to VBForums

There is a minor mistake on this line:

           LoadCombos ctrl.Name

.. change it to:

           LoadCombos ctrl

[frigidtorch]

Thank you so much for your reply...code now works.

If you don't mind asking, i'm still pretty much confused how pass by ref and pass by val work. Why didn't it work? ctrl.name would still give "Name1"?

[si_the_geek]

The problem was that ctrl.Name just passes a String, but the data type of the parameter for the sub is Combobox rather than String. It doesn't matter that the String happens to contain the name of a combobox, it is still just a String.

Removing the .Name means that the entire combobox gets passed, so the sub can then accept it, and work with it (in this case, adding items to the list).

[incidentals]

byval uses a copy of the value of the variable passed

byref uses the actual variables contents

byval can never change the variables external value - the one held outside the procedure

byref can change the value outside the procedure and causes "side affects" which are not always wanted

hope that clear that up

as si-the-geek said ctrl and ctrl.name may look the same but they are nothing like the same

think of the object ctrl as forrest gump's box of chocolates

you can ask forrest for the box or the name on the box

only one of these things will actually contain the chocolates

here to analogize ( I think thats a real word )