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, 2022 in Others by Kithuzzz
• 38,010 points
706 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, 2022 by narikkadan
• 63,420 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, 2022 in Others by narikkadan
• 63,420 points
470 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, 2022 in Others by narikkadan
• 63,420 points
1,338 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, 2022 in Others by narikkadan
• 63,420 points
729 views
0 votes
1 answer

How to use relative names in Excel VBA

It appears you are looking for Range.Offset() http://msdn.microsoft.com/en-us/library/office/ff840060%28v=office.15%29.aspx However, you ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 63,420 points
213 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, 2022 in Others by narikkadan
• 63,420 points
2,021 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,740 points
876 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
3,183 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, 2022 in Others by gaurav
• 23,260 points
481 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, 2022 in Others by narikkadan
• 63,420 points
5,957 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, 2022 in Others by narikkadan
• 63,420 points
910 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