Shape Names In Excel

0 votes

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

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
Nov 8 in Others by Kithuzzz
• 20,660 points
31 views

1 answer to this question.

0 votes

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
answered Nov 8 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

Excel issue "excel 4.0 function stored in defined names"

I don't want to revive an old ...READ MORE

answered Nov 5 in Others by narikkadan
• 37,660 points
20 views
0 votes
1 answer

Word Mail Merge with Excel data has to be saved in different files with custom names

Try this: Public Sub Mail_Merge() On Error GoTo ErrH Dim ...READ MORE

answered Nov 6 in Others by narikkadan
• 37,660 points
46 views
0 votes
1 answer

In a excel formula I need to create a list of names on one sheet based upon criteria/data of another sheet

The final formula is: =IF(ROWS($H$3:H3)<=$I$1,INDEX(Personnel! ...READ MORE

answered Nov 25 in Others by narikkadan
• 37,660 points
20 views
0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
531 views
0 votes
1 answer

Excel VBA to change background image of shape by clicking on shape

You need to keep track of what ...READ MORE

answered Sep 23 in Others by narikkadan
• 37,660 points
224 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
452 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,219 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
69 views
0 votes
1 answer

Get column names of Excel worksheet with OpenPyXL in readonly mode

This will print every thing from row ...READ MORE

answered Oct 22 in Others by narikkadan
• 37,660 points
367 views
0 votes
1 answer

Add a shape to a specific cell in Excel

When you use SORT() or data->sort on ...READ MORE

answered Oct 24 in Others by narikkadan
• 37,660 points
51 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP