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
FAMILY B
What I have to do:
What I would like to do:
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
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