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?

 

[Candyman8019]

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]

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]

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

 

[Fluff]

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]

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]

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.

 

[Candyman8019]

to test out the code, you can run it manually

 

[TPRshop]

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]

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