Hello all,

Picture this;

A spreadsheet containing some shapes, the number and type of shape
varies with user operation. All have been renamed from their defaul
names when they were created.

I need to group together some of these shapes using VBA according t
their current names. This is where I have run into the problem o
transferring the array contents into a range.

Sub GroupShapes_v1()
Dim aryGroup() As String, shp As Shape, i As Integer

For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 1) = "x" Then
ReDim Preserve aryGroup(i)
aryGroup(i) = shp.Name
i = i + 1
End If
Next shp

ActiveSheet.Shapes.Range(aryGroup).Select
Selection.ShapeRange.Group.Select

End Sub

However this generates an error at;
ActiveSheet.Shapes.Range(aryGroup).Select


So, I tried the following code after the For...Next loop instead

strGroup = Join(aryGroup, """,""")
ActiveSheet.Shapes.Range(Array(strGroup)).Select
Selection.ShapeRange.Group.Select

This then threw up an error at;
ActiveSheet.Shapes.Range(Array(strGroup)).Select


I think this would pose a similar problem to selecting multiple range
programmatically from a looped array.


Still no joy, but still confused.

Regards,

Boici

--
Message posted from http://www.ExcelForum.com

Personally I find shaperanges a pita to work with. So, an end run:

Sub GroupShapes_v2()
Dim aryGroup() As String, shp As Shape, i As Integer
Dim ShapeStr As String
For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 1) = "x" Then
ReDim Preserve aryGroup(i)
aryGroup(i) = shp.Name
i = i + 1
End If
Next shp
ActiveSheet.DrawingObjects(aryGroup).Select
End Sub


--
Jim Rech
Excel MVP

Aha! Managed to solve that one!

When looping through the shapes, it is possible to select multipl
items by using;

.Select False

instead of just;

.Select

which has the same effect as holding down the Ctrl button whil
selecting multiple shapes. e.g.



For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 1) = "x" Then shp.Select False
Next shp

Selection.ShapeRange.Group.Select



Boici

--
Message posted from http://www.ExcelForum.com