Use Hyperlink Function to Unhide Sheets

Question

I'm attempting to use a hyperlink to navigate to hidden sheets in an Excel workbook, but the code I'm using doesn't appear to recognize the target hyperlink, which has been created using the =Hyperlink() function. The hyperlink function is populated by a drop down list that contains all of the hidden sheet names. Does anyone know how I should be designating the target value? Or, if there is another way to get this to work?

Here's the code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim ShtName As String
ShtName = Target.Name
Sheets(ShtName).Visible = xlSheetVisible
Sheets(ShtName).Select
End Sub

Worksheet_FollowHyperlink does not work with functions created using the HYPERLINK function, that's correct. This approach may work. — BigBen
I ended up using "Worksheet_SelectionChange(ByVal Target As Range)" with the list validation drop down I created. It's buggy, but does roughly what I need. — AEL

AEL AEL · Accepted Answer · 2021-01-22T15:32:40

In case anyone runs into a very similar problem, here's a possible alternative for using a drop-down to navigate to hidden worksheets. Try using "Worksheet_SelectionChange(ByVal Target As Range)". Here's how I worked it out in my code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim WS As Worksheet
Dim name As String
If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub

name = Sheets("MasterTracker").Range("A2")
'MsgBox name
For Each WS In ActiveWorkbook.Worksheets
    If WS.Index > 4 Then
        If WS.name = name Then
            WS.Visible = xlSheetVisible
            WS.Activate
        End If
    End If
Next WS
End Sub

Use Hyperlink Function to Unhide Sheets

1 Answers