Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I group a set of shapes programmatically in excel 2007 vba?

Tags:

excel

shapes

vba

I am iterating over data on the Electrical Tables sheet and creating shapes on a Shape sheet. Once the shapes are created I would like to programmatically group them. However I can't figure out the right syntax. The shapes are there, selected, and if I click the group button, they group perfectly. However with the following code I get

Runtime Error 438 Object does not support this method or property.

I am basing this code on VBA examples off the web - I am not a strong VBA programmer. What is the right way to do this? I am working with excel 2007 and switching excel versions isn't an option.

Problematic snippet:

Set shapeSheet = Worksheets("Shapes")

With shapeSheet
    Selection.ShapeRange.Group.Select
End With

Context:

Dim shapeSheet As Worksheet
Dim tableSheet As Worksheet
Dim shpGroup As Shape

Set shapeSheet = Worksheets("Shapes")
Set tableSheet = Worksheets("Electrical Tables")


With tableSheet
    For Each oRow In Selection.Rows
            rowCount = rowCount + 1
            Set box1 = shapeSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 50, 50 + ((rowCount - 1) * 14), 115, 14)
            box1.Select (False)
            Set box1Frame = box1.TextFrame
            Set box2 = shapeSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 165, 50 + ((rowCount - 1) * 14), 40, 14)
            box2.Select (False)
            Set box2Frame = box2.TextFrame
     Next
End With

Set shapeSheet = Worksheets("Shapes")

With shapeSheet
    Selection.ShapeRange.Group.Select
End With
like image 871
Andrew Bucknell Avatar asked Jun 05 '11 20:06

Andrew Bucknell


2 Answers

No need to select first, just use

Set shpGroup = shapeSheet.Shapes.Range(Array(Box1.Name, Box2.Name)).Group

PS. the reason you get the error is that the selection object points to whatever is selected on the sheet (which will not be the shapes just created) most likely a Range and Range does not have a Shapes property. If you happened to have other shapes on the sheet and they were selected then they would be grouped.

like image 150
chris neilsen Avatar answered Sep 22 '22 21:09

chris neilsen


This worked for me in Excel 2010:

Sub GroupShapes()

    Sheet1.Shapes.SelectAll
    Selection.Group

End Sub

I had two shapes on sheet 1 which were ungrouped before calling the method above, and grouped after.

Edit

To select specific shapes using indexes:

Sheet1.Shapes.Range(Array(1, 2, 3)).Select

Using names:

Sheet1.Shapes.Range(Array("Oval 1", "Oval 2", "Oval 3")).Select
like image 33
Nick Spreitzer Avatar answered Sep 24 '22 21:09

Nick Spreitzer