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]

What is the name of the tab where your H2 dropdown exists?
In the code, wherever you see "sheet4", replace it with that tab name.
The value in H2 must have a tab with the same name with no extra spaces or whatever...otherwise you will get that error.

 

[TPRshop]

What is the name of the tab where your H2 dropdown exists?
In the code, wherever you see "sheet4", replace it with that tab name.
The value in H2 must have a tab with the same name with no extra spaces or whatever...otherwise you will get that error.

ya, after doing that it no longer has an error, but it also seems to not unhide teh tabs

 

[Candyman8019]

To Clarify, if H2 has the value of "BobsBurgers", there MUST be a tab with the name "BobsBurgers"...not "Bobs Burgers" or "BobsBurgers ", otherwise it will throw an error.

 

[TPRshop]

it is however working to hide them

 

[Candyman8019]

did you manually kick off the routine? or create a button to run it?

 

[TPRshop]

To Clarify, if H2 has the value of "BobsBurgers", there MUST be a tab with the name "BobsBurgers"...not "Bobs Burgers" or "BobsBurgers ", otherwise it will throw an error.

yup, the names on the dropdown are being pulled directly from the sheetnames

 

[TPRshop]

did you manually kick off the routine? or create a button to run it?

hmm ok now with a linked button it is working, is there a way to run it and the hyperlink in one click

 

[Candyman8019]

Give this a try, rather than creating a button, let's run the macro as soon as a value is changed in H2: So, add this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$2" Then
Call UnhideByLink
End If
End Sub

 

[TPRshop]

Give this a try, rather than creating a button, let's run the macro as soon as a value is changed in H2: So, add this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$2" Then
Call UnhideByLink
End If
End Sub

THANK YOU, you helped so much, now if i can figure out how to resolve this thread XD

 

[Candyman8019]

You're very welcome.