CODEWITHSUNDEEP
vbaexcel
Stücke
Stücke

Reputation: 993

Dynamic labelling of shapes

I am creating shapes within an For-loop and I want each shape having a different name. Therefore, the Shape in Set Shape = ... in each iteration should have Shape replaced by a dynamic variable.

If I place shapes via Set Shape = w.Shapes.AddShape(msoShapeRectangle, 10,10,10,10) how can I have Shape (the name of the shape) be dynamic e.g. Set Cells(1 + i, 1) = w.Shapes.AddShape(msoShapeRectangle, 10,10,10,10) ... so that each shape has a different name. I also tried Shape.Name = which does not seem to have the same effect as setting the name while creating the shape.

I assign a name for each shape which I create within the loop: Shape.Name = Cells(GanttStartRow + i, 1) & Cells(GanttStartRow + i, 2)

I set the connector via Set conn = w.Shapes.AddConnector(msoConnectorElbow, 1, 1, 1, 1) conn.ConnectorFormat.BeginConnect D, 1 conn.ConnectorFormat.EndConnect WP, 1 ... but receive a "type mismatch" error.

Upvotes: 0

Views: 1299

Answers (1)

FunThomas
FunThomas

Reputation: 29592

Assuming ws is the worksheet you are working with:

Dim s As Shape, i as integer
for i = 1 to 5
    Set s = ws.Shapes.AddShape(msoShapeRectangle, 50 + i * 120, 200, 100, 100)
    s.Name = "MyShapeName" & i
next i

You can later access the shapes by name:

For i = 1 To 5
    Set s = ws.Shapes("MyShapeName" & i)
    s.Fill.BackColor.RGB = RGB(0, 255 - i * 50, i * 50)
    s.BackgroundStyle = i
Next i

However, an alternative is to loop over all shapes:

For Each s In ws.Shapes
    Dim i As Integer
    if left(s.Name, 1, 11) = "MyShapeName" then
        i = Val(Mid(s.Name, 12))
        s.Top = s.Top + i * 4
    end if
Next s

Upvotes: 2

Related Questions