Loop through combo boxes

[Paella1]

I have a number of combo boxes on a worksheet (not a userform).

I want to loop through them and populate the list froma named range.

How do I loop through the combo boxes? Ie for each <i>something</i> in <i>somethings</i>.

 

[wut]

For each cb in ActiveSheet.ComboBoxes
code (eg, cb.listfillrange:=range("x"))
Next

I don't know if the collection for "Comboboxes" is "comboboxes". Alternatively, if you have only comboboxes on your worksheet, you could use the collection "OLEObjects".

 

[Paella1]

How do I loop through OLEObjects? the command "for each cb in OLEObjects" generates a type mismatch.

Also what type of OLEObject is a combo box?

 

[Trebor76]

Hi Paol,

Based on this post try this:

For Each OLEObject In ActiveSheet.OLEObjects
        With OLEObject
            If InStr(.progID, "ComboBox") > 0 Then
                'Paol:o, put your relevant code here you that you wish to run when the _
                ActiveX control found on the activesheet is a ComboBox
            End If
            
        End With
    Next OLEObject

You can't use the type of an OLEObject (i.e. OLEObject.OLEType) as it returns 2 for all types of ActiveX controls.

HTH

Robert

 

[Paella1]

Thanks Robert. It works fine ... as far as the looping bit goes

Another problem though: if I try to populate the combo box using OLEobject.List I get "object doesn't support this property or method".

But if I use the full name of the combobox e.g. ComboBox1.List then it works fine.

 

[Domenic]

Try..

[font=Verdana]    [color=darkblue]For[/color] [color=darkblue]Each[/color] OLEObject [color=darkblue]In[/color] ActiveSheet.OLEObjects
        [color=darkblue]With[/color] OLEObject
            [color=darkblue]If[/color] InStr(.progID, "ComboBox") > 0 [color=darkblue]Then[/color]
                .ListFillRange = Range("benefittype").Name
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]Next[/color] OLEObject
    [/font]

So each ComboBox will have the same values?

 

[wut]

I was trying that earlier...

ActiveSheet.ComboBox1.ListFillRange = Range("A1:A10")

Gives type mismatch.

 

[Paella1]

Domenic that works a treat! Thanks all for your help.

 

[Domenic]

I was trying that earlier...

ActiveSheet.ComboBox1.ListFillRange = Range("A1:A10")

Gives type mismatch.

The ListFillRange property requires a string to set the worksheet range...

ActiveSheet.ComboBox1.ListFillRange = "A1:A10"

 

[Domenic]

Domenic that works a treat! Thanks all for your help.

You're welcome!