hyperlink to hidden worksheets

SumeshNair

New Member
Joined
Dec 24, 2016
Messages
3
Greetings to all. I have 4 worksheets, named "Home", "QA trending", "analyst trending", and "mastersheet". Home is where I have created hyperlinks to the other sheets. I have tried to write a code but it didn't work. Please find it below.

Private sub worksheet_followhyperlink (byVal Target As Hyperlink)
Link to = target.subaddress
Wherebang = InStr(1, Link to, "!")
If wherebang > 0 Then
Mysheet = Left (LinkTo, wherebang - 1)
Worksheets (mysheet).Visible = True
Worksheets(mysheet).Select
Myaddr = mid(LinkTo, wherebang + 1)
Worksheets (mysheet).Range(myaddr).Select
End If

End Sub

It will be of great help to me if someone can suggest a solution. Thanks.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
Are you asking to unhide the sheets in the hyperlink subaddress? If yes, try this...

Code:
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_FollowHyperlink([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Hyperlink)
    Sheets(Split(Replace(Target.SubAddress, "'", ""), "!")(0)).Visible = True
    Target.Follow
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Last edited:

SumeshNair

New Member
Joined
Dec 24, 2016
Messages
3
Yes, I am trying for two things:-
First, as you said, unhiding the worksheets.
Secondly, when one worksheet is active, others should be hidden. In other words, only one worksheet should be visible (apart from home sheet) at a time.

I have also created a 'back' hyperlink in each sheet to go back to the Home sheet.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
Put this in ThisWorkbook code module. It should work for all your sheets.

Code:
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Workbook_SheetFollowHyperlink([color=darkblue]ByVal[/color] Sh [color=darkblue]As[/color] [color=darkblue]Object[/color], [color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Hyperlink)
    Sheets(Split(Replace(Target.SubAddress, "'", ""), "!")(0)).Visible = True
    Target.Follow
    Sh.Visible = [color=darkblue]False[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 

SumeshNair

New Member
Joined
Dec 24, 2016
Messages
3
Thank you for your responses alpha.
I tried it, but didn't work..Don't know where it is going wrong
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
Thank you for your responses alpha.
I tried it, but didn't work..Don't know where it is going wrong

Elaborate. What didn't work? What happens? Did you put the code in the ThisWorkbook code module?
 
Last edited:
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,164,008
Messages
5,834,871
Members
430,324
Latest member
bosphoruskid

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