Looping through different comboboxes in an excel sheet

  • Thread starter Thread starter steven.wright
  • Start date Start date
S

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.
 
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.
 
Back
Top