vba Excel - 返回所选选项按钮的标题

Excel - Returning the caption of the selected option button

提问by Josh

Probably a silly question with a simple answer but I am a real novice when it comes to userforms.

可能是一个带有简单答案的愚蠢问题，但在用户表单方面，我是一个真正的新手。

I have "Frame 3" with 5 different option buttons (Dest1, Dest2, Dest3, Dest4, Dest5) After an option is selected, where is the caption value of the selected option stored? How can I access that with vba.

我有带有 5 个不同选项按钮（Dest1、Dest2、Dest3、Dest4、Dest5）的“第 3 帧” 选择一个选项后，所选选项的标题值存储在哪里？我如何使用 vba 访问它。

Thank you, Josh

谢谢你，乔希

回答by Alex D

Here's just some example code you can use. Add your Option Buttons to groups, and then you can go from there. I used groups since you had multiple frames, and you can check based on group, and have multiple groups, and check which one's selected for each group.

这里只是一些您可以使用的示例代码。将您的选项按钮添加到组中，然后您就可以从那里开始。我使用了组，因为您有多个框架，您可以根据组进行检查，并有多个组，并检查为每个组选择了哪个。

Private Sub CommandButton1_Click()
     Dim x As Control

     ' Loop through ALL the controls on the UserForm.
     For Each x In Me.Controls
         ' Check to see if "Option" is in the Name of each control.
         If InStr(x.Name, "Option") Then
             ' Check Group name.
             If x.GroupName = "Grp1" Then
                 ' Check the status of the OptionButton.
                 If x.Value = True Then
                     MsgBox x.Caption
                     Exit For
                 End If
             End If
         End If
     Next
End Sub

回答by Olle Sj?gren

You can also access the option buttons through the frame-ojbect that holds them (if you have other frames and controls you don't want to go through):

您还可以通过包含它们的 frame-ojbect 访问选项按钮（如果您有其他不想通过的框架和控件）：

Option Explicit

Sub Test()
    Dim oCtrl As Control

    '***** Try only controls in Frame3
    For Each oCtrl In Frame3.Controls
        '***** Try only option buttons
        If TypeName(oCtrl) = "OptionButton" Then
            '***** Which one is checked?
            If oCtrl.Value = True Then
                '***** What's the caption?
                Debug.Print "You have checked option " & oCtrl.Caption
                Exit For
            End If
        End If
    Next
End Sub

回答by Dave

In my case, I wanted the caption of the toggle that was selected in an option group to be passed on to a subform filter. e.g. choosing toggle "black" filters subform to all cars where strColour = "black".

就我而言，我希望将在选项组中选择的切换的标题传递给子表单过滤器。例如，选择切换“黑色”过滤器子表单到所有 strColour =“黑色”的汽车。

I ended up with this:

我结束了这个：

Private Sub OptionGroupName_Click()
    Dim Caption As String
    Caption = OptionGroupName.Controls.Item(OptionGroupName.Value - 1).Caption
    Me.SubformName.Form.Filter = "[SubformField] = """ & Caption & """"
    Me.SubformName.Form.FilterOn = True
End Sub

回答by M. Swartz

Not to dog pile on everyone else's options but I created a function that takes the radio group name and spits out the selected radios coresponding label caption. Was using it in Access not Excel. Only works provided you name your controls similarly.... i.e. (lblRadioButton1 & optRadioButton1)

不要在其他人的选项上乱七八糟，但我创建了一个函数，该函数采用无线电组名称并吐出所选无线电对应的标签标题。在 Access 中使用它而不是 Excel。只有在您为控件命名类似的情况下才有效......即（lblRadioButton1 & optRadioButton1）

Function GetSelectedRadioButtonCaption(ByVal optionGroupName As OptionGroup) As String
    Dim oCtrl As Control
    Dim oCtrl2 As Control
    Dim optionLabelName As String
    Dim optionLabelObject As Label
    Dim optionButtonObject As OptionButton

    For Each oCtrl In optionGroupName.Controls
        '***** Try only option buttons
        If TypeOf oCtrl Is OptionButton Then
            '***** Which one is checked?
            Set optionButtonObject = oCtrl
            If optionButtonObject.OptionValue = optionGroupName.Value Then
                '***** What's the caption?
                optionLabelName = Replace(oCtrl.Name, "opt", "lbl")
                For Each oCtrl2 In optionGroupName.Controls
                    If oCtrl2.Name = optionLabelName Then
                      Set optionLabelObject = oCtrl2
                      GetSelectedRadioButtonCaption = optionLabelObject.caption
                      Exit For
                    End If
                Next
            End If
            If GetSelectedRadioButtonCaption <> "" Then
                Exit For
            End If
        End If
    Next
Exit_GetSelectedRadioButtonCaption:
End Function

回答by bmgh1985

The Label Text associated with an Option Button is obtainable by using OptionButton1.Caption

与选项按钮关联的标签文本可通过使用获得 OptionButton1.Caption

If you are using a loop, just substitute the OptionButton1 with your variable for option buttons and it will pull through the one you need when conditions are met. eg:

如果您使用的是循环，只需将 OptionButton1 替换为选项按钮的变量，它就会在满足条件时通过您需要的按钮。例如：

For xitem = 1 To 5
    xFrm = "OptionButton" & xitem
    For Each fItem In Me.Controls
        If fItem.Name Like xFrm Then
                If fItem.Value Then
                    k = fitem.Caption
                End If
        End If
    Next fItem
Next xitem