How can I fill 16 comboboxes with the same data using vba?

Alex00001

New Member
Joined
Sep 5, 2006
Messages
8
Hi,

I am not an experienced user and I have an userform with 16 comboboxes. The boxes are named CoBo_IN_01_01, CoBo_IN_01_02 to CoBo_IN_04_04. They have to be filled with the following items: 0, 10, 20, 30, to 100.

I know how to fill the boxes in VBA code one by one using add item. THis is very slow by the way.
But I think it's possible to do it a lot quicker. Is it possible to fill the comboboxes automatically using a next i, next j construction for the box names?

I would greatly appreciate any help!

Thanks


Alex
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
hi alex.

welcome to the board!

you can use .Column() = Array(...) to load your combobox in one step. to loop through your comboboxes, you could follow a similar technique as below. to use this example, create a new userform with three comboboxes named ComboBox1, ComboBox2, ComboBox3

Code:
Private Sub UserForm_Initialize()
    Call Test
End Sub
Sub Test()

    Dim i As Long
    
    For i = 1 To UserForm1.Controls.Count
        MsgBox UserForm1.Controls("ComboBox" & i).Name
    Next i
    
End Sub

cheers. ben.
 

Alex00001

New Member
Joined
Sep 5, 2006
Messages
8
Thanks for your quick response!
For my example it's vital the comboboxes remain named CoBo_IN_01_01 etc.
In which way can I use your trick for my comboboxes?
Is there a better way than the stone age programming I've done so far?
I'm struggling with the following code:

Code:
 For ".CoBo_IN_0" & "i" & "_0" & "j" 
                .Clear
                .AddItem "0"
                .AddItem "10"
                .AddItem "20"
                .AddItem "30"
                .AddItem "40"
                .AddItem "50"
                .AddItem "60"
                .AddItem "70"
                .AddItem "80"
                .AddItem "90"
                .AddItem "100"

                      If VBA_Ship_Product_Combi_IN_0 & "i" & "_0" & "j" = 1 Then .value = 0
                If VBA_Ship_Product_Combi_IN_0 & "i" & "_0" & "j" = 2 Then .value = 10
                If VBA_Ship_Product_Combi_IN_0 & "i" & "_0" & "j" = 3 Then .value = 20
                If VBA_Ship_Product_Combi_IN_0 & "i" & "_0" & "j" = 4 Then .value = 30
                If VBA_Ship_Product_Combi_IN_0 & "i" & "_0" & "j" = 5 Then .value = 40
                If VBA_Ship_Product_Combi_IN_0 & "i" & "_0" & "j" = 6 Then .value = 50
                If VBA_Ship_Product_Combi_IN_0 & "i" & "_0" & "j" = 7 Then .value = 60
                If VBA_Ship_Product_Combi_IN_0 & "i" & "_0" & "j" = 8 Then .value = 70
                If VBA_Ship_Product_Combi_IN_0 & "i" & "_0" & "j" = 9 Then .value = 80
                If VBA_Ship_Product_Combi_IN_0 & "i" & "_0" & "j" = 10 Then .value = 90
                If VBA_Ship_Product_Combi_IN_0 & "i" & "_0" & "j" = 11 Then .value = 100
        Next i
    Next j
[/quote]
 

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
alex.

rather than using additem, you could use the .List property (demonstrated below). it looks like you've named your Combi_IN such that the codes line up with your ComboBoxes, so i've added that to the code as well -- i'm not sure what these are, so that might be problematic. furthermore, if the names don't line up, you'll need to do something similar to what you have currently.

cheers. ben.

Code:
Sub LoadCombos()
    
    For i = 1 To 4
        For j = 1 To 4
            With UserForm1.Controls("CoBo_IN_0" & i & "_0" & j)
                .Clear
                .List() = Array(0, 10, 20, 30, 40, 50, 60, 70, 80, 100)
                .Value = ((VBA_Ship_Product_Combi_IN_0 & "i" & "_0" & "j") - 1) * 10
            End With
        Next j
    Next i
                   
End Sub

ben.
 

Forum statistics

Threads
1,141,281
Messages
5,705,474
Members
421,397
Latest member
JBenn911

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top