时间戳

首页 > 解决方案 > VBA：选中的组框数组中的复选框

arrays - VBA：选中的组框数组中的复选框

问题描述

我正在运行一个子程序，我需要计算组框中选中复选框的数量，并对几个组框执行此操作。编辑：我忘了提到我使用的是表单控件而不是 ActiveX 控件。

我的第一个问题是创建一组组框。我尝试使用

GB_Array = Activesheet.Shapes.Range(Array(Cells(x, y), Cells(z, y))) ' x,y,z defined elsewhere

我可以通过手动添加来完成这项工作，但这并不理想。我的第二个问题是这部分：

Option Base 1
Dim cbox as Checkbox
Dim C_cbox as Integer

GB_Array = Array("Name1", "Name2") ' Manually adding groupboxes to the array

For i = 1 to Ubound(GB_Array, 1)
  For Each cBox In Activesheet.Shapes.Range(GB_Array(1))
    If cBox.Checked = True Then
         C_cbox = C_cbox + 1
    End If
  Next cBox
Next i

返回类型不匹配错误 13。 编辑：似乎我犯了使用复选框对组框进行分组的错误，答案适用于“ugnrouped”组框（因此我可以在没有复选框的情况下移动组框）。

标签： arraysexcelvba

解决方案

这是你正在尝试的吗？

我的假设：所有控件都是表单控件。

我已经对代码进行了注释，因此您理解它应该没有问题。不过，如果您有任何疑问，请直接询问:)

Sub Sample()
    Dim ws As Worksheet
    Dim gbox As GroupBox
    Dim Shp As Shape
    Dim rngGBox As Range
    Dim C_cbox As Integer

    '~~> Change this to the relevant sheet
    Set ws = Sheet1

    With ws
        '~~> Loop through group boxes
        For Each gbox In .GroupBoxes
            '~~> Get the range of the groupbox
            Set rngGBox = .Range(gbox.TopLeftCell, gbox.BottomRightCell)

            '~~> Loop through all shapes
            For Each Shp In gbox.Parent.Shapes
                If Shp.Type = msoFormControl Then
                    '~~> Check if the shape is within the groupbox range
                    If Not Intersect(Shp.TopLeftCell, rngGBox) Is Nothing Then
                        If Not Shp Is gbox Then
                            '~~> Check if it is a checkbox
                            If Shp.FormControlType = xlCheckBox Then
                                '~~> Check if it is checked
                                If Shp.ControlFormat.Value = xlOn Then
                                    C_cbox = C_cbox + 1
                                End If
                            End If
                        End If
                    End If
                End If
            Next Shp
        Next gbox
    End With
End Sub

如果您想使用特定的组框，那么您可以使用它

Sub Sample()
    Dim ws As Worksheet
    Dim grpBxNames As String
    Dim grpBxArray As Variant
    Dim gbox As GroupBox
    Dim Shp As Shape
    Dim rngGBox As Range
    Dim C_cbox As Integer

    '~~> Change this to the relevant sheet
    Set ws = Sheet1

    '~~> Put the names separated by comma
    '~~> we will create the array during runtime
    grpBxNames = "Group Box 1,Group Box 6"
    grpBxArray = Split(grpBxNames, ",")

    With ws
        '~~> Loop through array of group boxes
        For i = 1 To UBound(grpBxArray)
            '~~> Set you object
            Set gbox = .GroupBoxes(grpBxArray(i))

            '~~> Get the range of the groupbox
            Set rngGBox = .Range(gbox.TopLeftCell, gbox.BottomRightCell)

            '~~> Loop through all shapes
            For Each Shp In gbox.Parent.Shapes
                If Shp.Type = msoFormControl Then
                    '~~> Check if the shape is within the groupbox range
                    If Not Intersect(Shp.TopLeftCell, rngGBox) Is Nothing Then
                        If Not Shp Is gbox Then
                            '~~> Check if it is a checkbox
                            If Shp.FormControlType = xlCheckBox Then
                                '~~> Check if it is checked
                                If Shp.ControlFormat.Value = xlOn Then
                                    C_cbox = C_cbox + 1
                                End If
                            End If
                        End If
                    End If
                End If
            Next Shp
        Next
    End With
End Sub

推荐阅读