Excel VBA add hyperlink to shape to link to another sheet

Question

I have a macro that creates a summary sheet at the front of a Workbook. Shapes are created and labeled after the sheets in the workbook and then hyperlinks are added to the shapes to redirect to those sheets, however, when I recorded the macro to do this, the code generated was:

ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(1), Address:=""

The hyperlinks that were manually created in excel while recording the macro work just fine and when hovering over them, display the file path and " - Sheet!A1" but they don't seem to actually be adding the link location into the address portion of the macro. Does anyone know the code that should go in that address section to link to the sheet?

Solution

The macro recorder doesn't record what is actually happening in this case. The property you are looking for is SubAddress. Address is correctly set in your code.

Create a hyperlink from a shape without selecting it
You want to avoid selecting things in your code if possible, and in this case it definitely is. Create a shape variable and set it to the shape you want to modify, then add the hyperlink to the sheet the shape is on. Note that you can also set the text for the screen tip.

In the example below, the shape I want to modify is on Sheet 6, and hyperlinks to a range on Sheet 4.

Sub SetHyperlinkOnShape()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet6")

    Dim hyperLinkedShape As Shape

    Set hyperLinkedShape = ws.Shapes("Rectangle 1")

    ws.Hyperlinks.Add Anchor:=hyperLinkedShape, Address:="", _
        SubAddress:="Sheet4!C4:C8", ScreenTip:="yadda yadda"
End Sub

Comments

[Adam Smith]

This works great, thanks for the help and best practices!