Using OLEObjects with grouped controls

McAldo

New Member
Joined
Feb 19, 2010
Messages
11
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:

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):

Code:
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,088
This code works for me:

Code:
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
 
L

Legacy 98055

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

Code:
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

New Member
Joined
Feb 19, 2010
Messages
11
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!
 

Forum statistics

Threads
1,148,158
Messages
5,745,108
Members
423,924
Latest member
Gazzat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top