Selecting a specific shape in a group of shapes

[JTBorton]

I have several shapes on my spreadsheet grouped together that represent a sort of control panel. When the user selects one of the radio options, I want it change the text of one of my buttons (which happens to be a bevel auto-shape assigned to a macro, called bxSortFitler). The shape and the two radio buttons are together in one group, called gpSortFilter. I have made several attempts, and despite spending a substantial amount of time trying to mimic and modify excel's help-file examples, all have been epic fail. Can anyone point me in the proper way to do this?

Failed Attempt 1:

Dim objButton As Object
    Set objButton = ActiveSheet.Groups("gpSortFitler").Groupitems("bxsortfitler")
    objButton.TextFrame.Characters.Text = "Sort"

Failed Attempt 2:

Dim objButton As Shape
    objButton.Groupitems("bxsortfilter").TextFrame.Characters.Text = "Sort"

Failed Attempt 3:

Dim objButton As Shape
    Set objButton = ActiveSheet.Shapes.Groupitems("bxsortfilter")
    .TextFrame.Characters.Text = "Sort"

And the list of failed attempts goes on.

Before grouping the shapes together it worked fine with the following code:

Dim objButton As Object
    Set objButton = ActiveSheet.Shapes("bxsortfilter")
    objButton.TextFrame.Characters.Text = "Sort"
    Set objButton = Nothing

-JTBorton
Another Day, Another Disaster

 

[PHV]

What about this ?

Dim objButton As Object
Set objButton = ActiveSheet.Shapes("gpSortFitler").Groupitems("bxsortfitler")
objButton.TextFrame.Characters.Text = "Sort"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JTBorton]

Thats actually one of the failed attempts that I didn't list, PHV. Set objButton = ActiveSheet.Shapes("gpSortFitler") works just fine if you cut it off there, but it doesn't like the .Groupitems("bxsortfitler"). Invalid Procedure Call.

-JTBorton
Another Day, Another Disaster

 

[JTBorton]

Okay so I think I've pretty much figured it out. You cannot select a shape out of a group of shapes in excel versions < 2007. For 2003 and below you must ungroup, make the desired change, then regroup.

Public Sub UngroupSortFilterItems()
Dim wkSht As Excel.Worksheet
Dim myRange As ShapeRange
    Set wkSht = Worksheets("AIL Manager")
    Set myRange = "gpSortFilter"
    myRange.Ungroup
    Set myRange = Nothing
    Set wkSht = Nothing
End Sub

Make desired Changes
.
.
.

Public Sub GroupSortFilterItems()
Dim wkSht As Excel.Worksheet
Dim myRange As ShapeRange
    Set wkSht = Worksheets("AIL Manager")
    Set myRange = wkSht.Shapes.Range(Array("fmeSortFilter", "lblSortBy", "optSortAIL", "optFilterAIL", "cboSortFilter", "bxSortFilter", "gpStackFilters", "bxStackFilters"))
    With myRange
        .Group
        .Name = "gpSortFilter"
    End With
    Set myRange = Nothing
    Set wkSht = Nothing
End Sub

-JTBorton
Another Day, Another Disaster