CODEWITHSUNDEEP
vbaexcelshapes
Abram
Abram

Reputation: 13

Shape Names In Excel

I have a VBA sub to create a few shapes, these shapes are then renamed to a cell value (B5:B15) and add text (C5:C15).

How it looks in Excel

The shapes gets created, renamed and the text gets added but when I try to connect them I get the "Object Required". Can some one please help me. Thanks in advance.

Sub Button1_Click()
Dim s, conn  As Shape, i As Integer
Set w = ActiveSheet

For i = 5 To 7

    Set s = w.Shapes.AddShape(1, 800, i * 120 - 599, 100, 100)
    s.Name = Range("B" & i)
    s.TextFrame.Characters.Text = Range("C" & i)
    s.Fill.ForeColor.RGB = RGB(0, 0, 213)
    s.TextFrame.Characters.Font.ColorIndex = 19

Next i

Set conn = w.Shapes.AddConnector(1, 1, 1, 1, 1)
    conn.ConnectorFormat.BeginConnect A001, 1
    conn.ConnectorFormat.EndConnect A002, 1   
End Sub

Upvotes: 1

Views: 2934

Answers (1)

Vityata
Vityata

Reputation: 43575

Something that would work:

Option Explicit

Sub Button1_Click()

    Dim s As Shape, conn As Shape, i As Long
    Dim w As Worksheet
    Set w = ActiveSheet

    Dim arr As Variant
    ReDim arr(5 To 7)
    For i = 5 To 7

        Set s = w.Shapes.AddShape(1, 800, i * 120 - 599, 100, 100)
        s.Name = Range("B" & i)
        s.TextFrame.Characters.Text = Range("C" & i)
        s.Fill.ForeColor.RGB = RGB(0, 0, 213)
        s.TextFrame.Characters.Font.ColorIndex = 19
        Set arr(i) = s
    Next i

    Set conn = w.Shapes.AddConnector(1, 1, 1, 1, 1)
    conn.ConnectorFormat.BeginConnect arr(5), 1
    conn.ConnectorFormat.EndConnect arr(6), 1

End Sub

What is the difference?

  • declaration of all variables - s is a Shape, i is a Long, w is a Worksheet;
  • the declaration is forced by Option Explicit;
  • a new variable arr is introduced, which keeps all the newly created forms. Thus the first form is kept under arr(5) and the last form is arr(7);
  • the BeginConnect and EndConnect need a variable which is a form. This is where we use the arr(5) to arr(7);

You can also refer to the shape, by its name and the Shapes() collection. Thus, the last 3 lines should look like this:

Set conn = w.Shapes.AddConnector(1, 1, 1, 1, 1)
conn.ConnectorFormat.BeginConnect w.Shapes("A001"), 1
conn.ConnectorFormat.EndConnect w.Shapes("A002"), 1

Upvotes: 1

Related Questions