Cycling through controls in worksheet
 

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

Cycling through controls in worksheet
 

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" wrote in message
...

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

Cycling through controls in worksheet
 

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 'The controls of which object?
'als je blad bedoelt dan wordt het Blad.Controls
Debug.Print ctrl.Name
Next ctrl
Next blad

End Su
-------------------

--
Dnere
-----------------------------------------------------------------------
Dnereb's Profile: http://www.excelforum.com/member.php...fo&userid=2618
View this thread: http://www.excelforum.com/showthread.php?threadid=39756

Cycling through controls in worksheet
 

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

--
DeeJa
-----------------------------------------------------------------------
DeeJay's Profile: http://www.excelforum.com/member.php...nfo&userid=542
View this thread: http://www.excelforum.com/showthread.php?threadid=39756

Cycling through controls in worksheet
 

Never mind. I used this:


Code
-------------------
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 Su
-------------------


And it works

--
DeeJa
-----------------------------------------------------------------------
DeeJay's Profile: http://www.excelforum.com/member.php...nfo&userid=542
View this thread: http://www.excelforum.com/showthread.php?threadid=39756