CODEWITHSUNDEEP
vba
JKnight
JKnight

Reputation: 41

VBA Add Anchor (Hyperlink) to Active Sheet

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

Upvotes: 2

Views: 2113

Answers (1)

Ibo
Ibo

Reputation: 4329

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

Upvotes: 2

Related Questions