Infizi
09-04-2007, 03:16 AM
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
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