CODEWITHSUNDEEP
excelvba
pmc086
pmc086

Reputation: 55

Excel VBA - loop through Form Control checkbox not working

So for whatever reason, I'm having trouble getting my VBA code to loop through the form control checkboxes on a worksheet in order to have a 'select all' check box. I came across the following two different methods that supposedly work in doing this:

Method 1:

Sub SelectAll_Click()

Dim CB As CheckBox
For Each CB In ActiveSheet.CheckBoxes
  If CB.Name = ActiveSheet.CheckBoxes("cbSiteAll").Name Then
    MsgBox CB.Name & ": " & CB.Value, vbOKOnly
    CB.Value = ActiveSheet.CheckBoxes("cbSiteAll").Value
  Else
    MsgBox CB.Name & ": " & CB.Value, vbOKOnly
  End If
Next CB
End Sub

Method 2:

Sub SelectAll_Click()

Dim CB As Shape
Dim sh As Worksheet

Set sh = ActiveSheet
For Each CB In sh.Shapes
  If CB.Type = msoFormControl Then
    If CB.FormControlType = xlCheckBox Then
      MsgBox CB.Name, vbOKOnly
      If CB.Name <> Application.ActiveSheet.CheckBoxes("cbSiteAll").Name Then
        CB.Value = Application.ActiveSheet.CheckBoxes("cbSiteAll").Value
        MsgBox xCheckBox.Name & ": " & xCheckBox.Value, vbOKOnly
      End If
    End If
  End If
Next CB
End Sub

The message boxes I entered to try and debug what was happening. For both of the above, the loop starts but the only message box I get relates to the box that I ticked to which I've assigned the macro. It does not appear to loop through any of the other checkboxes (although the validation for the checkbox works as the loop at least recognises the item ticked/unticked as a checkbox.

I have no idea why either of these are not working given the above and I've spent a fair bit of time looking for answers that address this specific issue and working through the logic to myself to no avail. Alas, I hand it over to the internet to see if they can help.

Thanks in advance.

Upvotes: 1

Views: 1450

Answers (2)

Ambie
Ambie

Reputation: 4977

For the benefit of others coming across this post, the Shape object contains a GroupItems property. This property, which is still a Shape object, is actually a collection of Shapes (https://msdn.microsoft.com/VBA/Excel-VBA/articles/shape-groupitems-property-excel). So when you iterate the Shapes list, you will only access the 'top-level' shapes; in other words, grouped shapes can only be accessed via the GroupItems property.

If you have grouped shapes on your worksheet that you wish to include in your For Each... loop, then one solution would be to iterate recursively. Something like the code below:

Option Explicit

Public Sub RunMe()
    RecursiveLoop Sheet1.Shapes
End Sub

Private Sub RecursiveLoop(col As Object)
    Dim shp As Shape

    For Each shp In col
        If IsGrouped(shp) Then
            RecursiveLoop shp.GroupItems
        Else
            Debug.Print shp.Name
        End If
    Next
End Sub

Private Function IsGrouped(shp As Shape) As Boolean
    Dim grp As GroupShapes

    On Error Resume Next
    Set grp = shp.GroupItems
    On Error GoTo 0

    IsGrouped = (Not grp Is Nothing)
End Function

Upvotes: 2

pmc086
pmc086

Reputation: 55

So after a bit of fiddling, I stumbled across the answer to the above. The issue was that I'd grouped the remaining check boxes and for whatever reason, this prevented them from being picked up in the loop in the above code. I ungrouped them and all of a sudden the code works. I am not sure why VBA/Excel does this but if anyone can explain I'd like to know why.

Thanks.

Upvotes: 0

Related Questions