How can I hyperlink to multiple hidden tabs from the same sheet?

Question

I've got a list of hyperlinks leading to multiple different hidden sheets in a workbook, using the following for each:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Application.ScreenUpdating = False
    Worksheets("LL - JLL").Visible = xlSheetVisible
    Sheets("LL - JLL").Visible = True
    Sheets("LL - JLL").Select
    Application.ScreenUpdating = True
End Sub

From what I can tell this now applies to every hyperlink on the sheet. Eevery hyperlink now leads to the same sheet, LL - JLL, whereas I would need each hyperlink to lead to a different sheet. For example,

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Application.ScreenUpdating = False
    Worksheets("LL - JLL").Visible = xlSheetVisible
    Sheets("LL - JLL").Visible = True
    Sheets("LL - JLL").Select
    Application.ScreenUpdating = True

    Application.ScreenUpdating = False
    Worksheets("LL - EMS").Visible = xlSheetVisible
    Sheets("LL - EMS").Visible = True
    Sheets("LL - EMS").Select
    Application.ScreenUpdating = True

    Application.ScreenUpdating = False
    Worksheets("LL- CCURE").Visible = xlSheetVisible
    Sheets("LL- CCURE").Visible = True
    Sheets("LL- CCURE").Select
    Application.ScreenUpdating = True
End Sub

The following code makes all hyperlinks on the sheet lead to the LL-CURE sheet, rather than their correspondent sheets.

Creating a new Sub for different hyperlinks leads to

Compile error:
Ambiguous name detected: Worksheet_FolowHyperlink

Any guidance would be greatly appreciated :)

Siddharth Rout Siddharth Rout · Accepted Answer · 2021-01-21T14:41:25

Logic:

Find the range which the hyperlink is pointing to
Find the name of the sheet to which the above range refers to
Pass the name to a common sub to unhide and activate the sheet

Code:

Is this what you are trying?

Option Explicit

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim rng As Range
    
    '~~> Get the range the hyperlink is referrig to
    Set rng = Application.Evaluate(Target.SubAddress)
    
    '~~> Unhide and activate the sheet
    UnHideAndActivate rng.Parent.Name
End Sub

Private Sub UnHideAndActivate(shName As String)
    Dim scrnUpdating As Boolean
    Dim dsplyAlerts As Boolean
    
    On Error GoTo Whoa
    
    With Application
        '~~> Get user's current setting
        scrnUpdating = .ScreenUpdating
        dsplyAlerts = .DisplayAlerts
        
        '~~> Set it to necessary setting
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    '~~> Unhide and activate the sheet
    Worksheets(shName).Visible = xlSheetVisible
    Worksheets(shName).Activate
LetsContinue:
    With Application
        '~~> Reset original settings
        .ScreenUpdating = scrnUpdating
        .DisplayAlerts = dsplyAlerts
    End With
    
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

How can I hyperlink to multiple hidden tabs from the same sheet?

1 Answers