CODEWITHSUNDEEP
vbaexcelshapes
A Cohen
A Cohen

Reputation: 456

VBA - Create, edit, and hyperlink shape

In my tool I am trying to create a shape, rename it, have it move with the shifting widths of columns, and hyperlink it to a summary sheet. This is what I have so far, thanks in advance. 

For s = 7 To Sheets.Count
    With Sheets(s)
        Dim GoToSummary As Shape
        Set GoToSummary = .Shapes.AddShape(msoShapeRoundedRectangle, 400, 153 + 12.75 * 2, 300, 50)

        .Shapes(GoToSummary).TextFrame.Characters.Text = "Go Back To Summary"
    End With
Next s

I know this is not correct, that is why I am reaching out, because I couldn't find anything similar to my situation.

Upvotes: 2

Views: 983

Answers (1)

barvobot
barvobot

Reputation: 897

You were pretty close!

Sub test()
Dim GoToSummary As Shape

For s = 7 To Sheets.Count
    Set GoToSummary = Sheets(s).Shapes.AddShape(msoShapeRoundedRectangle, 400, 153 + 12.75 * 2, 300, 50)
    GoToSummary.TextFrame.Characters.Text = "Go Back To Summary"
    Sheets(s).Hyperlinks.Add Anchor:=GoToSummary, Address:="", SubAddress:="Summary!A1"
Next s

End Sub
  • Dim GoToSummary outside of the loop
  • Once you've defined GoToSummary with Set, you can just refer to it directly, i.e. as GoToSummary instead of .Shapes(GoToSummary)
  • Added the hyperlink as well

Upvotes: 2

Related Questions