5

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
Amit Kohli
  • 2,860
  • 2
  • 24
  • 44
  • 1
    well, how would you define the groups/families? Are the buttons inside Frames? How would you know when a group starts and ends? It's not possible to achieve this without some sort of iteration or without an extra variable to store the `true` buttons name at all times. –  Mar 31 '14 at 15:29
  • The group/family name is a property called GroupName. it sets all to be the same group by default, but you can change that property to create new families. So each radiobutton knows what group it belongs to because it has that saved in the GroupName property. – Amit Kohli Mar 31 '14 at 18:59
  • Buttons inside frames would seem to be a **lot** more reliable than the GroupName property. In any case, you are certainly going to have to iterate each group in a `For...Next` loop with an `Exit For` statement when the `True` is found. This is not particularly complicated. I will see if I can imagine something that might be easier with your set-up. – David Zemens Mar 31 '14 at 19:15
  • I was thinking of a class (List) that stores all the true ones, since there can only be one true for family, you only need to store the family and it's current true value. This would only require as much iteration as the list is long = amount of families. –  Mar 31 '14 at 21:18

2 Answers2

2

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
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • Yessir! You got me close enough. I used your script, modifying a few things. Instead of creating a dictionary object, I stored everything in an array (because the group names are a bit random). This way I can query the groupname, and get the corresponding TRUE most sexily! Thanks again. – Amit Kohli Apr 01 '14 at 08:53
2

If you don't want to build a dictionary, this will get the selected option button for a group. The property .Controls in the accepted answer doesn't seem to exist in Excel 2010.

Function GetSelectedRadioButton(oSheet As Worksheet, groupName As String) As MSForms.optionButton
    Dim OleObj As OLEObject

    For Each OleObj In oSheet.OLEObjects
        If OleObj.progID = "Forms.OptionButton.1" Then
            If OleObj.Object.groupName = groupName Then
                If OleObj.Object.Value = True Then
                    Set GetSelectedRadioButton = OleObj.Object
                    Exit Function
                End If
            End If
        End If
    Next OleObj

    Set GetSelectedRadioButton = Nothing
End Function
Jbjstam
  • 874
  • 6
  • 13