如何用VBA检查Excel中的组框单选按钮中选中了哪个单选按钮

vptzau2j  于 2023-05-19  发布在  其他
关注(0)|答案(2)|浏览(243)

我有一个表单控件组框在Excel工作表中,我有多个单选按钮,其中只有一个按钮将被选中,我需要找出哪个按钮被选中使用VBA。我已经写了这个代码,但它不工作,并得到错误,对象不支持该行的此属性“对于buttonGroup中的每个按钮。Controls”。请指导我一些方法来实现这一点。

Sub IdentifySelectedRadioButton()

    Dim buttonGroup As Object

    Dim button As Object

    ' Set the button group name
    

    Set buttonGroup = Worksheets("Sheet1").Shapes("Group Box 1").OLEFormat.Object
    

    ' Loop through each button in the group

    For Each button In buttonGroup.Controls

        ' Check if the button is selected

        If button.Value = True Then
 
            MsgBox "Selected radio button: " & button.Caption

            Exit Sub

        End If

    Next button

    ' If no button is selected

    MsgBox "No radio button selected."

End Sub
x7rlezfr

x7rlezfr1#

VBA不能很好地处理这种情况。通过在“监视”窗口中搜索值,我能够找到似乎有效的方法

Public Sub demo()
    Dim buttonGroup As Object
    Dim button As Object
    
    ' Get the button group
    Set buttonGroup = Worksheets("Sheet1").Shapes("Group Box 1").OLEFormat.Object
    
    ' Loop through each control on the page
    For Each button In Worksheets("Sheet1").Shapes
        Dim b As OptionButton
        ' Check if the button is selected
        If button.FormControlType = xlOptionButton Then
            Set b = button.OLEFormat.Object
            If b.Value = xlOn Then
                MsgBox "Selected radio button: " & b.Name & " , " & b.Text
                Exit Sub
            End If
        End If
    
    Next button
    
    ' If no button is selected
    MsgBox "No radio button selected."
End Sub

注意事项

  1. OptionButton的值是xlOn或xlOff -非True/False
    1.实际的optionButton属性位于button.OLEFormat.Object
    1.在监 windows 口中,变量b的GroupBox已经设置好,我可以看到它的名称,但是试图通过代码访问它会导致同样的错误。
    1.我没有继续研究这个问题,所以这个场景仅限于工作表上的单个GroupBox
    1.一个我几乎忘记了-控制是在工作表上不包含在groupbox
    虽然这不是一个完全令人满意的答案,但我希望这至少能给你一些线索,让你知道为什么你会受苦
xuo3flqw

xuo3flqw2#

Option Explicit

'PUT THIS PUBLIC FUNCTION IN A MODULE
'------------------------------------------------
Public Function getSelectedOptionOfGroup(ws As Worksheet, group As String) As Object
   Dim ob As Object, sh As Shape

   For Each sh In ws.Shapes
      If TypeName(sh.OLEFormat.Object) = "OLEObject" Then
         Set ob = sh.OLEFormat.Object.Object
         If Not ob Is Nothing Then
            If TypeName(ob) = "OptionButton" Then
               If ob.GroupName = group And ob.value Then
                  Set getSelectedOptionOfGroup = ob
                  Exit Function
               End If
            End If
         End If
      End If
   Next
End Function

'PUT THIS BUTTON CLICK EVENT IN A SHEET module
-----------------------------------------------
Private Sub CommandButton3_Click()
   Dim opt As Object
   Set opt = getSelectedOptionOfGroup(Me, "TEST_GROUP")
   If Not opt Is Nothing Then
      MsgBox (opt.Caption)
   End If
End Sub

相关问题