Userforms and options buttons

Jonnyoforem

New Member
Joined
May 22, 2015
Messages
32
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.

Code:
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
Code:

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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Try...

Code:
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()
    [color=darkblue]Dim[/color] Ctrl [color=darkblue]As[/color] Control
    [color=darkblue]Dim[/color] buttonName [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]For[/color] [color=darkblue]Each[/color] Ctrl [color=darkblue]In[/color] Me.Controls
        [color=darkblue]If[/color] TypeName(Ctrl) = "OptionButton" [color=darkblue]Then[/color]
            [color=darkblue]If[/color] Ctrl.Value = [color=darkblue]True[/color] [color=darkblue]Then[/color]
                buttonName = Ctrl.Name
                [color=darkblue]Exit[/color] [color=darkblue]For[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] Ctrl
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Hope this helps!
 
Upvote 0
Code:
Private Sub CommandButton1_Click()
    Dim Ctrl As Control
    Dim buttonName As String
    For Each Ctrl In Me.Controls
        [B][COLOR="#FF0000"]If TypeName(Ctrl) = "OptionButton" Then[/COLOR][/B]
            If Ctrl.Value = True Then
                buttonName = Ctrl.Name
                Exit For
            End If
        End If
    Next Ctrl
End Sub
There is nothing wrong with the highlighted line of code... I just want to show you and the readers of this thread an alternative, that's all. This is my preference because VB's Intellisense provides the OptionButton part after you type the first two letters "op", just hit the space bar after typing "op" and finish off with the word "Then".
Code:
If TypeOf Ctrl Is OptionButton Then
 
Last edited:
Upvote 0
Thank you Domenic and Rick! Worked like an absolute charm. Just out of curiosity, can you use this same idea for any type of form control within a userform? Let's say I wanted to reference a combobox instead of option buttons. Would the only change be


Code:
[/COLOR][COLOR=darkblue]If[/COLOR][COLOR=#333333] TypeName(Ctrl) = "ComboBox" [/COLOR][COLOR=darkblue]Then blah blah blah. [/COLOR][COLOR=darkblue]


And how do I give you a big check mark so future viewers of this thread know which answer was the most helpful?
 
Upvote 0
Thank you Domenic and Rick! Worked like an absolute charm. Just out of curiosity, can you use this same idea for any type of form control within a userform? Let's say I wanted to reference a combobox instead of option buttons. Would the only change be

Code:
[/COLOR][COLOR=darkblue]If[/COLOR][COLOR=#333333] TypeName(Ctrl) = "ComboBox" [/COLOR][COLOR=darkblue]Then blah blah blah. [/COLOR][COLOR=darkblue]

Yes. I would still suggest you use the method I posted... as soon as you type the space after the word "Is", all the possible control types will be listed for you... either you can scroll through the list using your arrow keys or just start typing the name (you don't have to use combinations of upper and lower case letter when you do so, something the method you quoted requires)... as soon as the list whittles down to the control you want, just hit the space bar to lock it in (and begin typing "Then" afterwards).


And how do I give you a big check mark so future viewers of this thread know which answer was the most helpful?
[/COLOR]
You don't... this forum does not have a way to mark a message as the "Answer"; however, there is a "Like" hyperlink in the bottom right corner of each posted message where you can indicate you that a particular message was useful or interesting to you... you can do this in any message of any thread.
 
Upvote 0
Rick

I don't know why but for some reason
MsgBox TypeName(Ctrl) = "OptionButton"
returns True

whereas

MsgBox TypeOf Ctrl Is OptionButton
returns False

???

Works as expected with ComboBox but for me (Excel 2010) doesn't work with OptionButton

M.
 
Upvote 0
Rick

I don't know why but for some reason
MsgBox TypeName(Ctrl) = "OptionButton"
returns True

whereas

MsgBox TypeOf Ctrl Is OptionButton
returns False

???

Works as expected with ComboBox but for me (Excel 2010) doesn't work with OptionButton
Thanks for noting that... it has been awhile since I've done a UserForm and I forgot about that "problem". In my old "compiled version of VB" days, there was no problem doing what I suggested, but Microsoft, in its infinite wisdom, decided to shortcut references to some of the controls on a UserForm, but not all of them. Controls on a userform appear to belong to the MSForms object... sometimes you do not have to reference it (such as for a ComboBox) and other times you do (such as for an OptionButton), but you can apparently always reference them through MSForms. So, typing MS. (that dot should be typed) will put the MSForms. part on the code line, then type the first letters of the control you want, or scroll to the control type you want and then type the space to complete the control's type name. Sorry for any confusion I may have caused with my original post.
 
Upvote 0
Rick,

Thank you for the explanation

TypeOf Ctrl Is MSForms.OptionButton

works as expected!

M.
 
Upvote 0
Rick,

Thank you for the explanation

TypeOf Ctrl Is MSForms.OptionButton

works as expected!
I just re-read what I wrote... just to be clear, you can always place MSForms in front of any standard UserForm control (I am not sure about the ones you add to the Toolbox manually), including those that do not need it (like a ComboBox). I know it is three additional characters (ms.) over what I posted earlier, but the method I suggested is still faster (in my option) than having to type the parentheses, quote marks and the fully spelled out control name (using the correct letter casing) that your method requires.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top