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?
 

Candyman8019

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

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

TPRshop

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

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

Candyman8019

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

ADVERTISEMENT

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

TPRshop

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

New Member
Joined
Jan 4, 2022
Messages
12
Office Version
  1. 2021
Platform
  1. Windows

ADVERTISEMENT

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

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

New Member
Joined
Jan 4, 2022
Messages
12
Office Version
  1. 2021
Platform
  1. Windows
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
 
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,957
Messages
5,767,308
Members
425,403
Latest member
MellieD

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