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.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
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,384
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,384
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:

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

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