Loop through objects/controls on worksheet

[Tom V]

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

 

[Ron de Bruin]

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

 

[Guest]

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" <[email protected]> wrote in

message news:[email protected]...