CODEWITHSUNDEEP
excelvbashapes
Ascorpio
Ascorpio

Reputation: 206

How to insert shape into specified cell using "for each shape in activesheet.shapes" in excel vba

I am asking for hint with following problem. How to add to specified cells shapes from activesheet? I am able to add shape when I know the name but don't know how to implement forumla for each shape in...

Currently I have something like this:

Sub loop()

Dim a As Integer
Dim b As Integer
Dim c As Integer

For a = 1 To 10
    For b = 1 To 10

        ActiveSheet.Shapes.AddShape("Shape_Name", Cells(a, b), Cells(j, k), 10).Select

    Next a
Next b
End Sub

But I need something using this:

For Each Shape In ActiveSheet.Shapes

Upvotes: 2

Views: 13947

Answers (1)

user2140173
user2140173

Reputation:

I am not sure what you need but try this

Dim shp As Shape

For Each shp In ActiveSheet.Shapes
    Debug.Print shp.Name
Next

This only iterates over the Shapes collection. So you need to have shapes in place in order to access them => logical.

If you are trying to add shapes you can't use the for each in .Shapes as the .Shapes collection would have been empty.

So once you know the shapes name you can

Sheets("Sheet1").Shapes("Rectangle 1").Copy
Sheets("Sheet2").Select
Range("B2").Select
Sheets(2).Paste

Upvotes: 4

Related Questions