How to make HYPERLINK function work on hidden cells

[joobalooba]

Hi all,

Wonder if you can help me with the below. I've had a look through the forums, and I've been unable to find a resolution!

I'm creating a monitoring tool for some colleagues. Each supplier has their own tab (called "Fake 1", "Fake 2" etc in the sheet I've shared) which my colleagues can fill in with relevant details.

I've then created a main page at the front which can be both a summarising sheet and a table of contents.

My difficulty comes with hyperlinks. In column F, I've used the HYPERLINK function to automatically create hyperlinks to each of the various tabs. The function uses the column B which automatically generates a list of sheets in use. But my colleagues will want to hide tabs as otherwise the whole sheet will get too unwieldy. But once you've hidden a tab, the HYPERLINK function cannot call that tab up anymore. So the front page does not work as a table of contents anymore.

In the sheet I shared, the hyperlink for Fake1 will work, but not for Fake2. This is because Fake2 is hidden.

Is there a VBA way to get out of this? I want to be able to create the hyperlinks automatically, hence the use of the HYPERLINK function. There could be umpteen number of sheets, so manually using CTRL+K would not work easily - so I think I have to use the HYPERLINK function.

A link to the test file can be found here: https://drive.google.com/file/d/1d1xkr9ypiYbulm3mJp_hxMZgaxFApSx-/view?usp=sharing

Thanks in advance!

 

[Warship]

Add this to your "Main page" Worksheet module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 6 Then
        On Error GoTo OutEarly
        With Sheets(Cells(Target.Row, 1))
            .Visible = True
            .Activate
        End With
    End If
OutEarly:
End Sub

 

[joobalooba]

Amazing. Thank you!