Hyperlink to Hidden Sheets (VBA) - troubleshooting

[DAT74]

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:

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:

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!

 

[Fluff]

Hi & welcome to MrExcel.
What is the sheet name that fails?

 

[DAT74]

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]

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.

 

[DAT74]

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]

Glad it's sorted & thanks for the feedback.