Filling multiple comboboxes

[djburnheim]

I'm trying to fill multiple comboboxes in a worksheet with the same items but am running into troubles. I've been able to do it if the comboboxes are in a userform but can not work the code when they are in a sheet. This is what I have so far.

***code***
Sub Macro1()

Dim ctrl As Control

For Each ctrl In Sheets(1)
If TypeName(ctrl) = "ComboBox" Then
ctrl.AddItem = "Yes"
ctrl.AddItem = "No"
End If
Next ctrl

End Sub
***end code***

Any suggestions?

Thanks in advance
Dave

 

[RobBroekhuis]

Try this:

Dim Ob as OLEObject
dim ctrl As Control

For Each Ob In Sheets(1).oleobjects
set ctrl=ob.object
If TypeName(ctrl) = "ComboBox" Then
ctrl.AddItem = "Yes"
ctrl.AddItem = "No"
End If
Next Ob



Rob

 

[Zathras]

This seems to work ok:

Option Explicit
Sub SetComboBoxes()
Dim Ob As OLEObject
  For Each Ob In Sheets(1).OLEObjects
    If TypeName(Ob.Object) = "ComboBox" Then
      With Ob.Object
        .Clear
        .AddItem "Yes"
        .AddItem "No"
        .AddItem "Maybe"
        .AddItem "Fer Sure"
      End With
    End If
  Next Ob
End Sub

 

[djburnheim]

Thanks for the help. Zathras' code is working perfectly.