Open hidden sheets with the hyperlink function

TPRshop

New Member
Joined
Jan 4, 2022
Messages
12
Office Version
  1. 2021
Platform
  1. Windows
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?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Candyman8019

Active Member
Joined
Dec 2, 2020
Messages
262
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 4, 2022
Messages
12
Office Version
  1. 2021
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,856
Messages
5,766,786
Members
425,378
Latest member
kapoor2892

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
Top