How to make HYPERLINK function work on hidden cells

joobalooba

New Member
Joined
Jun 29, 2018
Messages
4
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!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Add this to your "Main page" Worksheet module.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top