Using OLEObjects with grouped controls

[McAldo]

Hi Everybody.

I am trying to set all checkboxes and radio buttons on a spreadsheet to False every time a drop down list value changes and I am using the following code:

Dim ole As OLEObject

For Each ole In ActiveSheet.OLEObjects
    If ole.progID = "Forms.CheckBox.1" Or ole.progID = "Forms.OptionButton.1" Then
        ole.Object.Value = False
    End If
Next ole

However, this will only work fine with ungrouped ActiveX controls.
I have several panes of controls grouped together and these will not switch to False.
It seems like grouped controls becomes shapes and unfortunately I don't find a way to modify their value property via .GroupItems
However, I manage to modify their visibility.
I have tried statements like (commented and in bold):

For Each T In ActiveSheet.Shapes("Advertisement2").GroupItems
    T.Visible = True
    

  '  [B]If T.Type = "Forms.CheckBox.1" Then T.Object.Value = False[/B]
  '  [B]If T.Type = "Forms.CheckBox.1" Then T.Object.Value = 0[/B]

   ' [B]Worksheets("Form").OLEObjects.Value = True[/B]

    '[B] If TypeName(T) = "CheckBox" Then T.Value = True[/B]

    Next

The reason for keeping some controls grouped is to switch their visibility in groups (see full code below).
Also, not all checkboxes and radio buttons are associated to cells, I guess I could associate each of them to a cell and switch the cell values to false as a workaround.
However, it would be good to understand what I am doing wrong..

Any advice much appreciated!

Thanks,

Aldo

Complete code:

Private Sub AppType_Change()


Dim Status As Boolean
Dim SelectedAppType As String

SelectedAppType = ActiveSheet.AppType.Text

Dim ole As OLEObject

For Each ole In ActiveSheet.OLEObjects
If ole.progID = "Forms.CheckBox.1" Or ole.progID = "Forms.OptionButton.1" Then
ole.Object.Value = False
End If
Next ole

If SelectedAppType = "Advertisement" Then

For Each T In ActiveSheet.Shapes("Advertisement2").GroupItems
T.Visible = True



Else

For Each T In ActiveSheet.Shapes("Advertisement2").GroupItems
T.Visible = False

Next

End If

 

[John_w]

This code works for me:

Sub ClearGrouped()

    Dim ole As OLEObject
    Dim shp As Shape
    Dim i As Integer
        
    For Each shp In ActiveSheet.Shapes
        If Left(shp.Name, 5) = "Group" Then
            For i = 1 To shp.GroupItems.Count
                Set ole = shp.GroupItems(i).OLEFormat.Object
                If ole.progID = "Forms.CheckBox.1" Or ole.progID = "Forms.OptionButton.1" Then
                    ole.Object.Value = False
                End If
            Next
        End If
    Next
    
End Sub

 

[Legacy 98055]

John's code looks fine. Even so, here is another approach that will account for grouped groups using recursion.

Sub Run()
    UnCheckAll
End Sub

Sub UnCheckAll(Optional Shapes)
    Dim s As Shape, o As Object
    
    If IsMissing(Shapes) Then Set Shapes = ActiveSheet.Shapes
    For Each s In Shapes
        Select Case s.Type
            Case msoGroup
                UnCheckAll s.GroupItems
            Case msoOLEControlObject
                Set o = s.OLEFormat.Object.Object
                Select Case TypeName(o)
                    Case "CheckBox", "OptionButton"
                        o.Value = False
                End Select
        End Select
    Next
End Sub

 

[McAldo]

John, Tom
thank you very much for the help!
Today I am off sick so I did not have a chance to try the code yet, but I am sure it will work fine.
This was something I really could not figure out how to sort out, thank you very much!