Return selected radiobutton from group in Excel userform

Question

I have an Excel userform that has a number of radio (option) buttons grouped together.

Is it possible to reference the GroupName of the radio buttons to identify which one has been selected?

I've tried me.myGroup, but Excel doesn't recognise it.

If possible, I would like to write something like;

myVar = me.mygroup

Is this possible in Excel 2013?

score 4 · Accepted Answer · edited Mar 27 '19 at 10:42

If you have set the GroupName property on the option buttons like this:

Then you can refer to that property in a loop of the controls where you are looking to see that the control's TypeName is OptionButton and that the GroupName is a match:

Option Explicit

Private Sub CommandButton2_Click()
    Dim opt As MSforms.OptionButton

    Set opt = GetSelectedOptionByGroupName("MyGroup")

    If Not opt Is Nothing Then
        MsgBox opt.Name
    Else
        MsgBox "No option selected"
    End If

End Sub

Function GetSelectedOptionByGroupName(strGroupName As String) As MSforms.OptionButton

    Dim ctrl As Control
    Dim opt As MSforms.OptionButton

    'initialise
    Set ctrl = Nothing
    Set GetSelectedOptionByGroupName = Nothing

    'loop controls looking for option button that is
    'both true and part of input GroupName
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "OptionButton" Then
            If ctrl.GroupName = strGroupName Then 
                Set opt = ctrl
                If opt.Value Then
                    Set GetSelectedOptionByGroupName = opt
                    Exit For
                End If
            End If
        End If
    Next ctrl

End Function

Thanks for this Robin, works a treat! It's a shame you have to loop through all the controls to do this, I thought it would be nice to simply reference the group and return the selected item e.g. 0,1,2,3 etc — PeteBradshaw, Jan 03 '17 at 18:18

score 1 · Answer 2 · answered Jan 03 '17 at 12:11

Morning Pete,

You would need to assign a particular value to your variable in order to determine which button has been clicked.

Try something like

Private Sub OptionButton1_Click()

myVar = 1

End Sub

to use a specific value. You can access this subroutine automatically by double-clicking your radio button in the userform editor. This way, later in your code you can reference myVar to determine which action your script should take next, e.g.

If myVar = 1 Then
....
ElseIf myVar = 2 Then
....
End If

etc.

I can't really provide more specific advice without knowing more about what your code is attempting to do.

Hope that helps!

score 0 · Answer 3 · answered Jan 03 '17 at 12:09

This should get you on the right track. Loop through your controls and check if they are selected (TRUE in the case of radio buttons)

Private Sub CommandButton1_Click()
    For Each Control In UserForm1.Controls
        If Control.Value = True Then
            MsgBox Control.Name
            'MsgBox Control.Tag
        End If
    Next Control
End Sub

Return selected radiobutton from group in Excel userform

3 Answers3