Reference Grouped ActiveX Controls Using a Variable

[Sam Pickard]

I have 32 checkboxes in a few different groups, each box is named EasyReviewXXXXXCheckbox where the Xs is a different 5 letter code for each box. I'm trying to find a way to reference them using a variable without ungrouping them. The code below seems to skip over any objects that are grouped, I know that if need be I can just ungroup them but If there's an easy way to include grouped objects I would much rather keep them together. Thanks in advance for any help.

Sub test()
    Dim obj As Object
    With ActiveSheet
        For Each obj In .OLEObjects
            If Left(obj.Name, 10) = "EasyReview" And Right(ct.Name, 5) = "Check" Then MsgBox (Mid(ct.Name, 10, 5))
        Next obj
    End With
End Sub

 

[RoryA]

You could do something like this

   Dim shp                    As Shape
   Dim oGrp                   As GroupShapes
   Dim shpSub                 As Shape

   With ActiveSheet
      For Each shp In .Shapes
         On Error Resume Next
         Set oGrp = shp.GroupItems
         On Error GoTo 0
         If Not oGrp Is Nothing Then
            For Each shpSub In shp.GroupItems
               If Left(shpSub.Name, 10) = "EasyReview" And Right(shpSub.Name, 5) = "Check" Then MsgBox (Mid(shpSub.Name, 11, 5))
            Next shpSub
            Set oGrp = Nothing
         End If
      Next shp
   End With

 

[Sam Pickard]

Thank you very much, that answered my question, unfortunately I made the assumption that if .name worked .value would also work and it doesn't seem like that is the case. Do you know if there's a way to pull out the value when using Shape? This is what I thought would work but the line in red gives me an "Object doesn't support this property or method" error.

   Dim shp                    As Shape
   Dim oGrp                   As GroupShapes
   Dim shpSub                 As Shape
   With ActiveSheet
      For Each shp In .Shapes
         On Error Resume Next
         Set oGrp = shp.GroupItems
         On Error GoTo 0
         If Not oGrp Is Nothing Then
            For Each shpSub In shp.GroupItems
               If Left(shpSub.Name, 10) = "EasyReview" And Right(shpSub.Name, 5) = "Check" Then
                  MsgBox (Mid(shpSub.Name, 11, 5))
[COLOR=#ff0000]                  If shpSub.Value = True Then MsgBox (Mid(shpSub.Name, 11, 5) & " Is True")
[/COLOR]               End If
            Next shpSub
            Set oGrp = Nothing
         End If
      Next shp   
   End With

 

[Sam Pickard]

Nevermind, I figured out a way around this so I don't need to refer to each checkbox. Thank you very much for your help though RoryA

 

[RoryA]

Try:

If shpSub.oleformat.object.object.value = True