Loop through objects/controls on worksheet
 

Hi all

I'm sure this is possible but I don't know the proper
syntax.

I'd like to loop through all the command buttons (or
other controls such as option buttons) on a worksheet and
capture their names. Is this possible?

Any help would be appreciated!
Thanks a lot!

Tom

Loop through objects/controls on worksheet
 

Hi Tom

If you use controls from the control toolbox then try this

Sub test1()
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CommandButton Then
MsgBox obj.Name
End If
Next
End Sub

Sub test2()
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Then
'obj.Object.Value = True
MsgBox obj.Name
End If
Next
End Sub


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


"Tom V" wrote in message ...
Hi all

I'm sure this is possible but I don't know the proper
syntax.

I'd like to loop through all the command buttons (or
other controls such as option buttons) on a worksheet and
capture their names. Is this possible?

Any help would be appreciated!
Thanks a lot!

Tom

Loop through objects/controls on worksheet
 

Thanks a lot for the quick response Ron!!

-----Original Message-----
Hi Tom

If you use controls from the control toolbox then try
this

Sub test1()
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CommandButton Then
MsgBox obj.Name
End If
Next
End Sub

Sub test2()
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Then
'obj.Object.Value = True
MsgBox obj.Name
End If
Next
End Sub


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


"Tom V" wrote in
message ...
Hi all

I'm sure this is possible but I don't know the proper
syntax.

I'd like to loop through all the command buttons (or
other controls such as option buttons) on a worksheet
and
capture their names. Is this possible?

Any help would be appreciated!
Thanks a lot!

Tom


.