Userforms and options buttons

[Jonnyoforem]

I have a macro that allows me to loop through option buttons on my worksheet and then assign the name of the option button to the variable buttonName as shown in the code below.

Dim myButton As OptionButton
Dim buttonName As String

'Loop Through Option Buttons
For Each myButton In Worksheets("Search").OptionButtons
If myButton.Value = 1 Then
buttonName = myButton.Text
Exit For
End If
Next myButton

I want to do the same thing but instead of looping through option buttons on a worksheet, I have a user form with option buttons I want to loop through. It's very important that I'm able to assign a variable to whatever the name of the selected option button is because I'll be using that variable later in the code. What tweaks do i need to make to the above code to accomplish this? Thanks in advance for any help

 

[Marcelo Branco]

Rick

I had understood perfectly what you meant. Agree your method is faster and safer (no misspelling).

M.

 

[My Aswer Is This]

Rick is the "Man". He always has good suggestions. Thanks Rick.

 

[RoryA]

Just a word of caution on this (and one reason I generally use TypeName in this situation): if you are interested only in checkboxes on a form, you will find that this:

TypeOf ctl Is MSForms.CheckBox

returns True for Checkboxes, but also for OptionButtons and ToggleButtons.

TypeName returns the actual type of a control (or variable) whereas TypeOf detects whether an object implements a specific interface.

 

[Marcelo Branco]

Just a word of caution on this (and one reason I generally use TypeName in this situation): if you are interested only in checkboxes on a form, you will find that this:

TypeOf ctl Is MSForms.CheckBox

returns True for Checkboxes, but also for OptionButtons and ToggleButtons.

TypeName returns the actual type of a control (or variable) whereas TypeOf detects whether an object implements a specific interface.

Good to know!

M

 

[Rick Rothstein]

Just a word of caution on this (and one reason I generally use TypeName in this situation): if you are interested only in checkboxes on a form, you will find that this:

TypeOf ctl Is MSForms.CheckBox

returns True for Checkboxes, but also for OptionButtons and ToggleButtons.

TypeName returns the actual type of a control (or variable) whereas TypeOf detects whether an object implements a specific interface.

OMG Are you kidding me??!!!?!!?!

Obviously I did not know this, so thank you very much for bring it to my (our) attention. Really though, why on earth would Microsoft have done that? I mean, there is an MSForms.OptionButton and an MSForms.ToggleButton type member which can be checked against, so why did Microsoft think it necessary to widen the scope of MSForms.Checkbox to cover them? I just don't get it.