Grouping Objects

[NextYearSox]

I have a number of shapes that I am using as buttons (hotspots) to launch various subroutines (basically, they are being used as sorting buttons). I have these shapes grouped together such that they maintain their physical proximity to each other.

When each button is clicked, I'd like it to be highlighted (i.e., the background is turned yellow, or the text is bold, etc.). However, because they are grouped together, I am unable to do so within VB (I can manually). I thought that I might get around this problem by: (1) ungrouping the buttons; (2) highlighting the button; and (3) re-grouping the buttons. However, each time I regroup, the name of the group changes; so this doesn't seem to be a workable solution.

Any ideas on how to skin this particular cat?

 

[Andrew Poulsom]

If it's only the name that concerns you, assign it to a variable before ungrouping. Then after regrouping rename it to the original name.

 

[Greg Truby]

VBE - F2 key (Object explorer). Pick shape in the classes pane and then GroupItems in the members pane and tap your buddy, the F1 key. I think that'll get ya whatcha need.

 

[Joe Was]

The trick is to name your shapes, then use an Array to hold the group then you can unGroup and re-group using the array as indicated here:


Sub myTest()

Set myDocument = Worksheets(1)

With myDocument.Shapes
.AddShape(msoShapeCan, 50, 10, 100, 200).Name = "shpOne"
.AddShape(msoShapeCube, 150, 250, 100, 200).Name = "shpTwo"

With .Range(Array("shpOne", "shpTwo")).Group
.Fill.PresetTextured msoTextureBlueTissuePaper
.Rotation = 45
.ZOrder msoSendToBack
End With
End With

For Each s In myDocument.Shapes
s.Ungroup
Next
End Sub

 

[Greg Truby]

Fiddlesticks. If the items in question are truly shapes then all's ya need is:

    ActiveSheet.Shapes("MyShapeGroup").GroupItems(2).Fill.ForeColor.RGB = RGB(255, 0, 0)
    ActiveSheet.Shapes("MyShapeGroup").GroupItems(1).Fill.ForeColor.RGB = RGB(196, 196, 196)

However, having said that; I can't get this to work at all at changing the fill colors on actual buttons.

 

[NextYearSox]

This sounds like the general idea. I'm having some problems implementing this due to ignorance, I'm sure.

My code looks like the following:

Sub ButtonName()
ActiveSheet.Shapes("Rectangle 65").Name = "SortA"
ActiveSheet.Shapes("Rectangle 60").Name = "SortB"
ActiveSheet.Shapes("Rectangle 62").Name = "SortC"
ActiveSheet.Shapes("Rectangle 63").Name = "SortD"
ActiveSheet.Shapes("Rectangle 64").Name = "SortE"
ActiveSheet.Shapes("AutoShape 67").Name = "Background"

With .Range(Array("SortA", "SortB", "SortC", "SortC", "SortE", "Background")).Group
End Sub

The last shape ("Background") is the shape I want on the bottom (i.e., Order/Send to back). The other 5 shapes will be superimposed on top and separate from each other - so it doesn't matter their order.

The last line (with .Range(Array....) isn't doing the job. What code should I use to group these and ungroup these?

Thanks

 

[Greg Truby]

Joe's line is inside a WITH/END WITH structure, specifically

with mydocument.shapes

which is why that particular line begins with a period. My guess is that your line is not within a WITH structure.

 

[NextYearSox]

I've solved the group question with the following code:

ActiveSheet.Shapes.Range(Array("SortA", "SortB", "SortC", "SortD", "SortE", "Background")).Group

Everytime I group these, the new group has a new name (i.e., Group 75, then Group 76, etc.).

Is there a way to select the group without knowing it's name?
Is there a way to reset the group number?

 

[Greg Truby]

You can assign the name when ya group 'em

ActiveSheet.Shapes.Range(Array("rectangle 1", "rectangle 2", "rectangle 3")).Group.Name = "PickelBarrel"

 

[Joe Was]

'Array code here, then

Selection.ShapeRange.Group.Select
Selection.Name = "myShpGroup1"

I like Greg's better!