VBA: Checked checkboxes in array of groupboxes

Question

I am running a sub where I need to count the number of checked checkboxes in a groupbox and do this for several groupboxes. Edit: I forgot to mention I am using form controls and not ActiveX controls.

My first issue is creating an array of group boxes. I tried using

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

I can make that work by manually adding, but it isn't ideal. My second issue is with this part:

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

Returns type mismatch error 13. EDIT: Seems like I made the mistake of grouping the group box with the checkboxes, the answer works for "ugnrouped" groupboxes (so I can move the groupboxes without the checkboxes).

Siddharth Rout · Accepted Answer · 2019-01-17T11:01:00.780

Is this what you are trying?

My assumptions: All controls are form controls.

I have commented the code so you should not have a problem understanding it. Still if you do have any queries then simply ask :)

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

And if you want to work with specific group boxes then you can use this

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

My excuses :-), but I couldn't abstain from citing this recent comment regarding [flattening arrow code](https://stackoverflow.com/questions/54219683/non-contiguous-for-each-loop-per-row-instead-of-column/54220583#comment95269519_54220583). — T.M., Jan 17 '19 at 10:54
Nah it's ok. I created it so that it is easy for the user to understand what is happening. In a real scenario, I would combine the `Ifs` :) — Siddharth Rout, Jan 17 '19 at 10:57
Thank you, this works for me. EDIT: Not sure if this should be a seperate question, but do you have a neat way to select a range of names as the grpBxNames so I do not have to add them manually? — BlackBear, Jan 17 '19 at 11:03

score 0 · Answer 2 · answered Jan 17 '19 at 10:32

I don't believe you need an array of check boxes. Please look at the code below.

Sub ResetCheckBoxes()
  Dim Ctrl As OLEObject
  Dim n As Integer

  For Each Ctrl In ActiveSheet.OLEObjects
      If TypeName(Ctrl.Object) = "CheckBox" Then
            Debug.Print Ctrl.Object.GroupName, Ctrl.Object.Value
            Ctrl.Object.Value = True
      End If
  Next Ctrl
End Sub

The code loops through all ActiveX controls on the ActiveSheet and picks out CheckBoxes. It then prints the box's GroupName and Value properties before changing the Value. Run the code again to see the changed value.

The GroupName is the tab name by default. You can assign another value to it either manually, when you create the check box or using the above code. Once all check boxes within a particular group have the same GroupName you can add a further If condition to the above loop and pick out only those which belong to that particular group - and that fulfills your purpose of an array.

VBA: Checked checkboxes in array of groupboxes

2 Answers2

Linked