Hyperlink to Hidden Sheets (VBA) - troubleshooting

DAT74

New Member
Joined
Jun 29, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hi all, I am new here but have found good information in the past from this site.

I am creating a data-integrated sheet for my company that simply has a menu page with hyperlinked cells to travel to hidden corresponding sheets. The hidden sheets then have a "Back to Menu" hyperlink that re-hides the sheet. The Menu Page vba script for unhiding/go-to the hidden sheet(s) works for the first few links, then bugs out from the 5th link on (Run-time error '9': Subscript out of range).

Debugging shows the error here:

VBA Code:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    LinkTo = Target.SubAddress
    WhereBang = InStr(1, LinkTo, "!")
        If WhereBang > 0 Then
            mysheet = Replace(Left(LinkTo, WhereBang - 1), "'", "")
          [COLOR=rgb(184, 49, 47)] [B] [/B][/COLOR]Worksheets(mysheet).Visible = True                                 <------------ error is here
            Worksheets(mysheet).Select
            MyAddr = Mid(LinkTo, WhereBang + 1)
            Worksheets(mysheet).Range(MyAddr).Select
        End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Hyperlinks.Count > 0 Then
        Target.Hyperlinks(1).Follow
    End If
End Sub


The code for each page that hyperlinks back to the menu and re-hides is:

VBA Code:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Worksheets("INQUIRE").Select
    Target.Parent.Worksheet.Visible = False
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Hyperlinks.Count > 0 Then
        Target.Hyperlinks(1).Follow
    End If
End Sub


Like I said, this works great for the first few links, then I get the error. Any help would be greatly appreciated!
 
Last edited by a moderator:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,538
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
What is the sheet name that fails?
 

DAT74

New Member
Joined
Jun 29, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hi & welcome to MrExcel.
What is the sheet name that fails?
Hi Fluff, thank you.

The sheets that are failing so far have the following names: "AGEXT Director's Office" & "AGEXT Entomology"

The sheets that the hyperlinks work are named: "AGEXT General Notes", "AGEXT Economics", "AGEXT BAEN" -- so not sure why this would be different.

Also the menu page is named "INQUIRE" in the workbook.

Thanks!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,538
Office Version
  1. 365
Platform
  1. Windows
I can see no reason why this sheet would not work "AGEXT Entomology"
but "AGEXT Director's Office" will fail as you have an apostrophe in the name, which the code is removing & so cannot find the sheet. The easiest option would be to remove the ' from the sheet name.
 
Solution

DAT74

New Member
Joined
Jun 29, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
I can see no reason why this sheet would not work "AGEXT Entomology"
but "AGEXT Director's Office" will fail as you have an apostrophe in the name, which the code is removing & so cannot find the sheet. The easiest option would be to remove the ' from the sheet name.
Hi again, Fluffy,

Thanks so much, I had not realized that the code removes that character from sheet names - simply removing it was the fix!

As for the "entomology" sheet, I still got the error after fixing the other sheet, but deleting the sheet and re-adding the hyperlinks works fine now! I suppose it was hung up on the prior error.

Thank you again so much!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,538
Office Version
  1. 365
Platform
  1. Windows
Glad it's sorted & thanks for the feedback.
 

Forum statistics

Threads
1,141,608
Messages
5,707,368
Members
421,505
Latest member
whittirs

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