PDA

View Full Version : How to concatenate ComboBox name



hmatt415
04-15-2015, 05:08 PM
So I 12 combo boxes with names "ComboBox1" through "ComboBox12" respectively. Each ComboBox has the same drop-down items. Instead of me coding each one individually, is there a way to loop through them? For example, I have the following code:

For c = 1 To 12
combo = "ComboBox" & c
With Sheet1.combo
.Clear
.AddItem "cat"
.AddItem "dog"
.AddItem "fish"
.AddItem "bird"
End With
Next

But it always says "combo" not recognized, or something down those lines.

Any help/suggestions are appreciated.

mancubus
04-15-2015, 09:39 PM
For i = 1 To 12
With Worksheets("Sheet1").Shapes("ComboBox" & i).OLEFormat.Object.Object
.Clear
.AddItem "cat"
.AddItem "dog"
.AddItem "fish"
.AddItem "bird"
.ListIndex = 0 'delete this line if a default value in combobox is not desired.
End With
Next

mancubus
04-15-2015, 09:56 PM
please use code tags when posting your code.

# button in Quick Reply will do it for you.

[ CODE ]paste your code here[ /CODE ]

another way:


For i = 1 To 12
With Worksheets("Sheet1").Shapes("ComboBox" & i).OLEFormat.Object.Object
.Clear
.List = Array("cat", "dog", "fish", "bird")
.ListIndex = 3 'delete this line if a default value in combobox is not desired.
End With
Next

snb
04-16-2015, 03:04 AM
Sub M_snb()
sheet1.oleobjects("combobox1").object.list=Array("cat", "dog", "fish", "bird")

for j=2 to 12
sheet1.oleobjects("combobox" & j).object.list=sheet1.oleobjects("combobox1").object.list
next
End Sub

NB. The use of '.clear' in combination with '.List' is redundant: '.List' replaces the existing List.

mancubus
04-16-2015, 02:01 PM
thanks for the reminder snb.