excelvbaradio-buttonuserformgroupname

Return selected radiobutton from group in Excel userform


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?


Solution

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

    enter image description here

    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