vba 从组中获取真正的单选按钮 val

vba get true radio button val from group

提问by Amit Kohli

In Excel VBA:

在 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

家庭A

• Option1 - F
• Option2 - T

• 选项 1 - F
• 选项 2 - T

FAMILY B

家庭 B

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

• 选项 11 - F
• 选项 12 - F
• 选项 13 - F
• 选项 14 - F
• 选项 15 - 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)

• 选项 1 是真的吗？
• 选项 2 是真的吗？（是的......所以A家庭的选项2）
• Option11 是真的吗？
• Option12 是真的吗？
• Option13 是真的吗？
• Option14 是真的吗？
• Option15 是真的吗？（是的……所以家庭 B 的 Option15）

What I would like to do:

我想做什么：

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

• 家庭 A 的哪个按钮是真的？（选项 2）
• 哪个按钮对 B 族来说是真的？（选项 15）

---

---

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

采纳答案by David Zemens

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

这样的事情似乎有效。我已经把它放在一个 CommandButton 点击​​事件处理程序中，但你可以把它放在任何地方。

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

回答by Jbjstam

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.

如果您不想构建字典，这将获得一个组的选定选项按钮。Excel 2010 中似乎不存在已接受答案中的属性 .Controls。

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