vba get true radio button val from group

Amit Kohli picture Amit Kohli · Mar 31, 2014 · Viewed 26.3k times · Source

In Excel VBA:

I'm creating a form. This form has several radio button groups, some of which have many options (but only one radiobutton can be true per group). I would like to be able to get the name of the radiobutton that's "true" per group, instead of having to check every radio button's condition.

For example:

FAMILY A

  • Option1 - F
  • Option2 - T

FAMILY B

  • Option11 - F
  • Option12 - F
  • Option13 - F
  • Option14 - F
  • Option15 - T

What I have to do:

  • Is Option1 True?
  • Is Option2 True? (yes... so Option2 for Family A)
  • Is Option11 True?
  • Is Option12 True?
  • Is Option13 True?
  • Is Option14 True?
  • Is Option15 True? (yes... so Option15 for Family B)

What I would like to do:

  • What button is True for Family A? (Option2)
  • What button is True for Family B? (Option15)

Is this possible?

Thanks for looking!

EDIT: Solution! Based on David's advice below:

Dim ctrl As MSForms.Control
Dim dict(5, 1)
Dim i

'## Iterate the controls, and associates the GroupName to the Button.Name that's true.

For Each ctrl In Me.Controls
    If TypeName(ctrl) = "OptionButton" Then
        If ctrl.Value = True Then
            dict(i, 0) = ctrl.GroupName
            dict(i, 1) = ctrl.Name
            i = i + 1
        End If
    End If

Answer

David Zemens picture David Zemens · Mar 31, 2014

Something like this seems to work. I have put this in a CommandButton click event handler, but you could put it anywhere.

Sub CommandButton1_Click()

Dim ctrl As MSForms.Control
Dim dict As Object

Set dict = CreateObject("Scripting.Dictionary")


'## Iterate the controls, and add the GroupName and Button.Name
'  to a Dictionary object if the button is True.
'  use the GroupName as the unique identifier/key, and control name as the value

For Each ctrl In Me.Controls
    If TypeName(ctrl) = "OptionButton" And ctrl.Value = True Then
        dict(ctrl.GroupName) = ctrl.Name
    End If
Next

'## Now, to call on the values you simply refer to the dictionary by the GroupName, so:

Debug.Print dict("Family A")
Debug.Print dict("Family B")

Set dict = Nothing

End Sub