Cycling through controls in worksheet

Can someone tell me how to cycle through all the controls in a worksheet? I tried this:

Private Sub CommandButton1_Click()
Dim ctrl As Control
Dim blad As Worksheet

For Each blad In ActiveWorkbook.Worksheets
    Debug.Print blad.Name
    For Each ctrl In Controls
        Debug.Print ctrl.Name
    Next ctrl
Next blad

End Sub

But this resulted in an "object required" error. Apparently the worksheet object does not have a controls collection.

Private Sub CommandButton1_Click()
Dim ctrl As Control
Dim blad As Worksheet

For Each blad In ActiveWorkbook.Worksheets
    Debug.Print blad.Name
    For Each ctrl In Controls        'The controls of which object?
                                                  'als je blad bedoelt dan wordt het Blad.Controls 
        Debug.Print ctrl.Name
    Next ctrl
Next blad

End Sub

Hi DeeJay

You can use the Shapes collection

I have info about deleting controls here, but you can see how you must loop there
http://www.rondebruin.nl/controlsobjectsworksheet.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


"DeeJay" <[email protected]> wrote in message
news:[email protected]...
>
> Can someone tell me how to cycle through all the controls in a
> worksheet? I tried this:
>
>
> Code:
> --------------------
> Private Sub CommandButton1_Click()
> Dim ctrl As Control
> Dim blad As Worksheet
>
> For Each blad In ActiveWorkbook.Worksheets
> Debug.Print blad.Name
> For Each ctrl In Controls
> Debug.Print ctrl.Name
> Next ctrl
> Next blad
>
> End Sub
> --------------------
>
>
> But this resulted in an "object required" error. Apparently the
> worksheet object does not have a controls collection.
>
>
> --
> DeeJay
> ------------------------------------------------------------------------
> DeeJay's Profile: http://www.excelforum.com/member.php...fo&userid=5420
> View this thread: http://www.excelforum.com/showthread...hreadid=397561
>

How can I see what type my control is? I am using comboboxes but I'd like to know it for other controls as well.

Never mind. I used this:

Sub OLEObjects3()
'Delete/hide only all CommandButtons or ComboBoxes or ??? from the Control Toolbox
    Dim obj As OLEObject
    For Each obj In ActiveSheet.OLEObjects
        If TypeOf obj.Object Is MSForms.CommandButton Then
            obj.Delete
            ' or obj.Visible = False if you want to hide them
        End If
    Next
End Sub

And it works!