Why does grouping ActiveX checkboxes alter OLEObje

Question

I have some code that loops through the ActiveX controls on an Excel worksheet. This logs which checkboxes have been selected.

Dim obj AS OLEObject
For Each obj In ActiveSheet.OLEObjects
    If TypeName(obj.Object) = "CheckBox" Then ' loop through all checkboxes to find selections
        BooCheck = obj.Object
        If BooCheck = True Then
           MyArray(j) = obj.Name 'if checkbox selected then store the associated Name
           j = j + 1
        End If
    End If
Next obj

This all works fine. However, as I have a number of checkboxes that I need to move around I thought I'd group them together by Shift/click in design mode, right click and select the "Group" option. However, if I do this the grouped checkboxes vanish from OLEObjects. Where do they go? Is there a way of altering my code to find them when they are grouped?

Answer 1

The way to reference the OLEObjects is like this:

Public Sub ReferenceTest(oSheet As Worksheet, sGroupName As String)

    Dim i               As Long
    Dim oOle            As OLEObject

    With oSheet.Shapes.Range(sGroupName).GroupItems
        For i = 1 To .Count
            Set oOle = .Item(i).OLEFormat.Object
            Debug.Print oOle.Name, oOle.Object.Value
        Next i
    End With

End Sub

Just specify the sheet and group name, e.g.

ReferenceTest ActiveSheet, "Group 1"