Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through different comboboxes in an excel sheet
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through different comboboxes in an excel sheet
did you try UserForm1.ComboBox(j).
or UserForm1.ComboBox & j " wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through different comboboxes in an excel sheet
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. " wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping through all comboboxes in workbook and setting values with .additem | Excel Programming | |||
Fire events for all comboboxes in my sheet | Excel Programming | |||
looping thru each w/sheet | Excel Programming | |||
Looping Through Sheet | Excel Programming | |||
Looping through ComboBoxes in a worksheet | Excel Programming |