VBA Objects (getting a combobox)

[Infizi]

Hi guys, im creating an on the fly menu system to present some reports. i have the code below but im getting a problem getting objects.

Simply i need to get loop through any objects on my sheet and if its a combo box i want to add some stuff to it. The problem i run into is that once i have found the object i cant seem to get a reference for it without hard coding it - which defeats the point of the script.

combo boxes are just named combobox1, combobox2 etc etc

the aim is to be able to access the combobox's as csd so a user can add as many combos as required and the function will find each one and update them.

Thanks

Code:
Dim Sh As Worksheet
Dim CombO As Integer

Dim cBx As Object

Dim CsD As ComboBox

For Each cBx In Home.OLEObjects
If VBA.Left(cBx.Name, VBA.Len(cBx.Name) - 1) = "ComboBox" Then
CsD = cBx
CsD.Clear
CsD.AddItem "--- Please Select ---"
End If
Next cBx

[rory]

You need something like this:

Dim Sh As Worksheet
Dim CombO As Integer
Dim cBx As Object
Dim CsD As msforms.ComboBox
For Each cBx In ActiveSheet.OLEObjects
If TypeName(cBx.Object) = "ComboBox" Then
Set CsD = cBx.Object
CsD.Clear
CsD.AddItem "--- Please Select ---"
End If
Next cBx

[Infizi]

rock on! thanks mate. i see where my problem was now - i didnt define the combobox properly and i was referencing to a collection, not the actual object!

Thanks again