Group Shapes

[Zone]

I have a number of shapes on my worksheet. I want to group only SOME
of these. I can fill an array with the names of the shapes I want to
group, but how do I tell VBA which shapes to group together? TIA,
James

 

[Paul Mathews]

Hi James,

Perhaps something like this helps (below I'm grouping two shapes, one named
'Shape1' and the other name 'Shape2'):

Sub GroupShapes()
ActiveSheet.Shapes.Range(Array("Shape1", "Shape2")).Select
Selection.ShapeRange.Group.Select
End Sub

You could replace the array item with your array.

 

[Zone]

Thank you for your reply, Paul. My problem is, I don't know how many
shapes will be affected. What I did was create a dynamic array and
redim preserve it as many times as needed to add the shape names to it.
ActiveSheet.Shapes.Range(Array(myArray)).select
won't work, but I don't know how to put my array in the place of
Array("Shape1","Shape2"). This seems to be an array of names, but
they're shown explicitly. I could probably get the shapes' index
numbers instead, but I don't know if that will help. Any ideas? James

 

[Jim Cone]

James,
The array containing the names needs to be an array of variants,
not a variant containing an array...
Dim MyArray() as Variant

Load your array with the particular shape names, then use it like this...
Dim shpGroup as Excel.ShapeRange
Set shpGroup = ActiveSheet.Shapes.Range(MyArray)
shpGroup.Group
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Zone" <[email protected]>
wrote in message
I have a number of shapes on my worksheet. I want to group only SOME
of these. I can fill an array with the names of the shapes I want to
group, but how do I tell VBA which shapes to group together? TIA,
James

 

[Greg Wilson]

Following is demo code that does a few of the basics. Of note:
1. The dynamic array must be of type Variant for reasons I'm not aware.
2. You can't change the textframe of a group shape while grouped but you
can change most other properties. Fill color example shown in the code. You
have to ungroup to change the textframe then regroup.
3. Adding text to a rectangle changes it to a textbox. It will behave
differently thereafter.

Sub Test()
Dim s As Shape
Dim sr As ShapeRange
Dim grp As Shape
Dim arr() As Variant 'Must be Variant (don't know why)
Dim i As Integer

i = 0
'demo adding names of red colored shapes to array
For Each s In ActiveSheet.Shapes
If s.Fill.ForeColor.RGB = vbRed Then
ReDim Preserve arr(i)
arr(i) = s.Name
i = i + 1
End If
Next
'demo grouping shapes named in array
Set grp = ActiveSheet.Shapes.Range(arr).Group
'demo changing property of specific group item without ungrouping
grp.GroupItems(1).Fill.ForeColor.RGB = vbYellow
DoEvents
'demo changing property of entire group object
grp.Top = grp.Top + 200
DoEvents
'demo failure of changing textframe of grouped item (raises error)
'grp.GroupItems(1).TextFrame.Characters.Text = "Test 1234"

'demo ungrouping and assigning items to shaperange variable
Set sr = grp.Ungroup
'demo iterating through shaperange
For i = 1 To sr.Count
MsgBox sr(i).Name
Next
'demo changing textframe of item when not grouped (succeeds)
sr(1).TextFrame.Characters.Text = "Test 1234"
'demo regrouping
Set grp = sr.Regroup
End Sub

Regards,
Greg

 

[Zone]

Jim and Greg, Many thanks for your replies. This works great. I was
on the right track and was pretty close, but I tripped over explicitly
declaring the array of type Variant, and I needed the Excel.ShapeRange
object. Jim, your comments gave me the specific solution to the
problem, but both your replies were very helpful to me. Thanks again!
Best regards, James