Open hidden sheets with the hyperlink function

[TPRshop]

I need to use the hyperlink function to a hidden sheet, the function is changing based off an auto updating drop down list. I have been scouring google to no avail. I even found another thread here i thought would solve it but didn't.

in h2 is a dropdown list of all my sheet names that are hidden, and in h5 is a hyperlink with this function: =HYPERLINK("#'"&H2&"'!A1","Jump to field")
the only thing I really need is it to open when I click the hyperlink, and close when I leave it. Any suggestions?

 

[TPRshop]

You're very welcome.

any chance there is a way to a named list of sheets when I go to the main list instead of just one?

 

[Candyman8019]

For sure... you can use a Loop to loop through your list of tab names. Change the worksheet activate routine to something like this:

Private Sub Worksheet_Activate()
Dim tabname As String
TabList = 2
Do While Worksheets("Sheet4").Cells(TabList, 14) <> Empty
tabname = Sheets("Sheet4").Cells(TabList, 14).Value
Sheets(tabname).Visible = False
TabList = TabList + 1
Loop
End Sub

To set this up, you need to know where your list of tab names is. In my example, it is on sheet4...starts on row 2 (TabList value) in column N (column 14). this will start at N2 and hide tab names down as it loops down the column until it encounters an empty cell.

 

[TPRshop]

For sure... you can use a Loop to loop through your list of tab names. Change the worksheet activate routine to something like this:

Private Sub Worksheet_Activate()
Dim tabname As String
TabList = 2
Do While Worksheets("Sheet4").Cells(TabList, 14) <> Empty
tabname = Sheets("Sheet4").Cells(TabList, 14).Value
Sheets(tabname).Visible = False
TabList = TabList + 1
Loop
End Sub

To set this up, you need to know where your list of tab names is. In my example, it is on sheet4...starts on row 2 (TabList value) in column N (column 14). this will start at N2 and hide tab names down as it loops down the column until it encounters an empty cell.

Thank you so much, I'd be lost without the help of geniuses like you.