1

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.

Improve this question
4
  • Method 2 will work if you change CB.Value to Application.ActiveSheet.CheckBoxes(CB.Name).Value, and (judging by your subroutine name) probably change "cbSiteAll" to "SelectAll". I don't use form controls often enough to know whether this is the easiest way to do it, but I do know that it worked for me. Commented Aug 21, 2017 at 0:36
  • Thanks. I'll get to that bit once I get the loop to work properly. Turns out because my check boxes were grouped, the code wasn't looping through them. Not sure why but it seems to have fixed the looping issue that I was having. Commented Aug 21, 2017 at 0:37
  • Method 1 will work if you change the If CB.Name = ActiveSheet.CheckBoxes("cbSiteAll").Name Then to If CB.Name <> ActiveSheet.CheckBoxes("cbSiteAll").Name Then - you were only changing the value of the "cbSiteAll" checkbox to be itself. Commented Aug 21, 2017 at 0:40
  • Metod2 will work. But MsgBox xCheckBox.Name & ": " & xCheckBox.Value, vbOKOnly is not good. MsgBox CB.Name & ": " & CB.Value, vbOKOnly is suitable. Commented Aug 21, 2017 at 3:29

2 Answers 2

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for that! This makes sense now and your code above is a good catch-all to prevent missing grouped items if you didn't know they were there.
0

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.