VBA Add Anchor (Hyperlink) to Active Sheet

Question

I'm trying to create a hyperlink from the active sheet to Sheet1. Everything works great except for the "Address" in the anchor tab. I'm not sure if I have the correct syntax for connecting the active sheet to sheet1. The active sheet will always have a different name each time, hence the reason for needing the syntax for the active sheet as apposed to giving it a specific name.

Set rng = Sheets("Sheet1").Range("A50").End(xlUp).Offset(1, 0) rng.Hyperlinks.Add anchor:=rng, Address:=ThisWorkbook.ActiveSheet, TextToDisplay:=UserForm2.TextBox4.Value

Ibo Ibo · Accepted Answer · 2017-07-19T17:08:14

Sub CreateHLFromActiveSheetToSheet1()
    Set Rng = ActiveSheet.Range("A50").End(xlUp).Offset(1, 0)
    ActiveSheet.Hyperlinks.Add anchor:=Rng, Address:="", SubAddress:=Sheets("Sheet1").Name & "!A1", _
        ScreenTip:="Click to go to Sheet1", TextToDisplay:="Go To Sheet1"

End Sub

Sub CreateHLFromSheet1ToActiveSheet()
    Set WS = Sheet1
    Set Rng = WS.Range("A50").End(xlUp).Offset(1, 0)
    WS.Hyperlinks.Add anchor:=Rng, Address:="", SubAddress:=ActiveSheet.Name & "!A1", _
        ScreenTip:="Click to go to Sheet1", TextToDisplay:="Go To " & ActiveSheet.Name

End Sub

VBA Add Anchor (Hyperlink) to Active Sheet

1 Answers