Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Controls in a Worksheet | Excel Programming | |||
Controls on worksheet | Excel Programming | |||
controls on worksheet | Excel Programming | |||
Worksheet Controls | Excel Programming | |||
Referencing to controls on a worksheet | Excel Programming |