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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Candyman8019

Active Member
Joined
Dec 2, 2020
Messages
282
Office Version
  1. 365
Platform
  1. Windows
Your formula works fine to hyperlink to another tab. The problem is you can't jump to a hidden tab. You'll likely need to invoke some VB when you click on that link that would unhide the specified tab and navigate to it. Then you'll need additional VB that will hide tabs when you leave them.
 

TPRshop

New Member
Joined
Jan 4, 2022
Messages
12
Office Version
  1. 2021
Platform
  1. Windows
Your formula works fine to hyperlink to another tab. The problem is you can't jump to a hidden tab. You'll likely need to invoke some VB when you click on that link that would unhide the specified tab and navigate to it. Then you'll need additional VB that will hide tabs when you leave them.
Ya, I had figured as much but I'm having issues finding a way to do that in vba, the code I have found online hasn't worked for me
 

Candyman8019

Active Member
Joined
Dec 2, 2020
Messages
282
Office Version
  1. 365
Platform
  1. Windows
Give this a try...
this code will hide whatever sheet is listed on sheet4 in cell H2.

Sub UnhideByLink()
Dim tabname As String
tabname = Sheets("Sheet4").Range("H2").Value
Sheets(tabname).Visible = True
End Sub

NOTE: There is no error handling build into this, ie if the value in H2 doesn't have a corresponding tab, it will error out.

In your main sheet, ie where H2 exists, add the following code to rehide the tab you just left:
Private Sub Worksheet_Activate()
Dim tabname As String
tabname = Sheets("Sheet4").Range("H2").Value
Sheets(tabname).Visible = False
End Sub
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,435
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: I need to unhide a sheet (idk if I put the last one in the right spot)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

TPRshop

New Member
Joined
Jan 4, 2022
Messages
12
Office Version
  1. 2021
Platform
  1. Windows
Give this a try...
this code will hide whatever sheet is listed on sheet4 in cell H2.

Sub UnhideByLink()
Dim tabname As String
tabname = Sheets("Sheet4").Range("H2").Value
Sheets(tabname).Visible = True
End Sub

NOTE: There is no error handling build into this, ie if the value in H2 doesn't have a corresponding tab, it will error out.

In your main sheet, ie where H2 exists, add the following code to rehide the tab you just left:
Private Sub Worksheet_Activate()
Dim tabname As String
tabname = Sheets("Sheet4").Range("H2").Value
Sheets(tabname).Visible = False
End Sub
hmm this seems to have done nothing... is there something I need to adjust in order to get this to work for me?
 

Candyman8019

Active Member
Joined
Dec 2, 2020
Messages
282
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Sheet4 will need to be replaced with whatever sheet your H2 exists on.
Then you will need to create a button or hyperlink a cell to invoke the UnhideByLink routine.
 

TPRshop

New Member
Joined
Jan 4, 2022
Messages
12
Office Version
  1. 2021
Platform
  1. Windows
Give this a try...
this code will hide whatever sheet is listed on sheet4 in cell H2.

Sub UnhideByLink()
Dim tabname As String
tabname = Sheets("Sheet4").Range("H2").Value
Sheets(tabname).Visible = True
End Sub

NOTE: There is no error handling build into this, ie if the value in H2 doesn't have a corresponding tab, it will error out.

In your main sheet, ie where H2 exists, add the following code to rehide the tab you just left:
Private Sub Worksheet_Activate()
Dim tabname As String
tabname = Sheets("Sheet4").Range("H2").Value
Sheets(tabname).Visible = False
End Sub
a download of my file can be found at I need to unhide a sheet (idk if I put the last one in the right spot)
 

TPRshop

New Member
Joined
Jan 4, 2022
Messages
12
Office Version
  1. 2021
Platform
  1. Windows
Sheet4 will need to be replaced with whatever sheet your H2 exists on.
Then you will need to create a button or hyperlink a cell to invoke the UnhideByLink routine
it seems to give a subscript out of range error
 
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,152,822
Messages
5,772,469
Members
425,760
Latest member
zj042060

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