Looping through different comboboxes in an excel sheet

[steven.wright]

I have a worksheet with 11 comboboxes and I wish to loop through each
and populate them using the .AddItem command. I am pulling the data
from a corresponding column in a worksheet (i.e. ComboBox1 gets data
from column 1,ComboBox2 from column 2 and so on). I have written the
code in a userform using the following code (this code was edited for
simplicity):

Dim i As Integer
Dim j As Integer

For j = 1 to 11
i = 2
Me.Controls("ComboBox" & j).Clear
Do
If Not Sheet5.Cells(i, j).Value = "" Then
Me.Controls("ComboBox" & j).AddItem Sheet5.Cells(i,
j).Value
End If
i = i + 1
Loop Until Sheet5.Cells(i, j).Value = ""
Me.Controls("ComboBox" & j).ListIndex = 0
Next j

Unfortunately I am not pleased with the userform approach and want to
do everything within an excel worksheet however vba within the excel
worksheet will not recognize the Controls("ComboBox" & j) portion of
the code. What can I do to replace this portion of the code so I can
cycle through the comboboxes within the worksheet. Any help would be
greatly appreciated. Thank you.

 

[Guest]

did you try UserForm1.ComboBox(j).
or UserForm1.ComboBox & j

 

[Guest]

Sorry, you are working directly on the worksheet. So you would need the
control type as the child to the worksheet instead of the UserForm.