I am using Office 2003 on Windows XP.

I need to loop through the ActiveX controls (i.e. those from the Control
Toolbox) on the currently active sheet and IF the control is an OptionButton,
determine if it is the option chosen, if so, return the name of the control
in a variable.

Please note that there are other types of AX controls on the sheet, but only
one set of OptionButtons so far, so only one return value. Something like:

Dim oName as String
Dim octrls as OLEObjects
Dim octrl as OLEObject
For Each octrl in octrls
If octrl is an OptionButton then
If octrl is UserChoice then
oName = octrl.Name
End If
End If
Next

Can someone please post some example code?
Thanks much in advance.

Dim oName as String
Dim octrls as OLEObjects
Dim octrl as OLEObject
Dim ob as MsForms.Optionbutton
set octrls = ActiveSheet.OleObjects
oName = ""
For Each octrl in octrls
If typeof octrl.Object is MSForms.OptionButton then
If octrl.Object.Value = True then
set ob = octrl.Object
oName = ob.Name
exit for
End If
End If
Next
if oName < "" then
msgbox oName & " was chosen"
else
msgbox "No choice made"
End if

--
Regards,
Tom Ogilvy


"XP" wrote in message
...
I am using Office 2003 on Windows XP.

I need to loop through the ActiveX controls (i.e. those from the Control
Toolbox) on the currently active sheet and IF the control is an
OptionButton,
determine if it is the option chosen, if so, return the name of the
control
in a variable.

Please note that there are other types of AX controls on the sheet, but
only
one set of OptionButtons so far, so only one return value. Something like:

Dim oName as String
Dim octrls as OLEObjects
Dim octrl as OLEObject
For Each octrl in octrls
If octrl is an OptionButton then
If octrl is UserChoice then
oName = octrl.Name
End If
End If
Next

Can someone please post some example code?
Thanks much in advance.