For Each ComboBox in Worksheet 1

[caffrinho]

Please help, I think the subject heading says it all really.
I have MANY comboboxes on a spreadsheet, and rather than write out the code for every cbo i'd like to do it with a For Each...Next loop.
The only problem is, i dunno how. (Believe me, this post is my last resort after hours of searching....)
I'm getting various errors with what i've come up with so far, like "Type mismatch error".


Thanks in advance!!!

 

[euskadi]

This will work assuming your shape names are consistent.

Sub findCombos()

Dim myobject As Shape
For Each myobject In ActiveSheet.Shapes
   If Left(myobject.Name, 5) = "Combo" Then
        MsgBox "hello"
        'Your Action Here
   End If
Next myobject

End Sub

 

[caffrinho]

Worked a treat! thanks very much...

 

[caffrinho]

OK, I lied. It did work for displaying the msgbox for every combobox on my worksheet. The only problem is, ideally i wanted to use the loop to fill *all* the comboboxes with the same values, so what i was doing before was, for every combobox...

With ComboBox1
.Clear
.AddItem "6a"
.AddItem "7a"
.AddItem "8a"
End With

I wanted to put this in a loop (as described) but this code doesn't work with a variable, i.e.

With myobject
.clear
.AddItem "6a"
.AddItem "7a"
.AddItem "8a"
End With

gives me an error: "Object doesn't support this property or method".


I would gratefully appreciate any further assistance.
Cheers,

C...