Shape Names In Excel

Question

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).

When I try to link the shapes, I receive the "Object Required" message even after I have created, renamed, and added the text. Could someone kindly assist me. I appreciate it.

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

Answer 1

Try this:

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