Dynamically add a hyperlink to a shape in VBA

[hamfinite]

I am trying to create a table of contents for a very long list of items on a sheet. I am using shapes on a seperate sheet to act as buttons, that go to the location on the correct sheet using a hyperlink. I found how to add a hyperlink using a macro, but I can't find how to create a hyperlink for the shape using a macro.

What is the code I would need to set a hyperlink to "Sheet1!A2:Sheet1!C2", with the a tooltip of "Some text"?

Here is how I create the shapes:

With myDocument.Shapes.AddShape(msoShapeRoundedRectangle, 10, IntVerticalOffset, 630, IntHeight) 
    .Fill.ForeColor.RGB = RGB(204, 255, 204)
    .Line.ForeColor.RGB = RGB(0, 0, 0)
    .TextFrame.Characters.Text = StrRowName
    .TextFrame.HorizontalAlignment = xlHAlignLeft
    .TextFrame.Characters.Font.ColorIndex = xlAutomatic
    .TextFrame.Characters.Font.FontStyle = "Bold"
    .Locked = True
End With

 

[hamfinite]

For the other part of your question, the hyperlink is attached to the shape, so if you delete the shape, the link is gone too.

Thank you again.

 

[xlsat]

In a related situation to the original post here, I am trying to attach a tooltip (through hyperlink route) and a macro to an existing shape. The code is something like this -

Sub testtooltip()
    Dim myDocument As Worksheet
    Dim shp As Shape
    Dim strTooltip As String, strMacroName As String
    
    Set myDocument = Sheets("MyDashboard")
    
    strTooltip = "setting this tooltip - "
    strMacroName = "'" & ActiveWorkbook.Name & "'" & "!" & "RefreshDashboard"
    
    With myDocument
        Set shp = .Shapes("shp_button_refresh")
        
        .Hyperlinks.Add Anchor:=shp, Address:="", ScreenTip:=strTooltip
       shp.OnAction = strMacroName
    End With

End Sub

I am calling this Sub on Workbook_Open. As I see, the tooltip gets assigned to the shape without any problem and also, the Macro name too seems to get assigned. BUT, on click of the shape, nothing happens, meaning, the assigned macro never gets called. If I comment out the tooltip assigning line, then macro gets called!!!

I did see similar macro not getting called problem being posted in a few places, but none of them seemed to provide a proper answer Help please?

Note: My Excel version is 2007.

 

[wgaskill]

In Excel 2007, you can add hyperlinks to a shape. When you refer to (Forms) buttons to add, they are "shapes". Can you add a hyperlink to the shape associated with a button?

 

[zarkus1621]

Here it is 9 years later and this thread was still very helpful. Thanks! Now I can automatically create tables of contents with clickable shapes for our charts of accounts.