VBA to link to hidden sheets with spaces in names

[Outdoorsman80]

I have a large Excel file with many work sheets. I want to hide the data sheets and keep only certain dashboards open. I created an Index sheet to link to the hidden ones.

The code in the Index:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim ShtName As String
        ShtName = Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1)
        Sheets(ShtName).Visible = xlSheetVisible
        Sheets(ShtName).Select
End Sub

The code in the hidden sheets to re-hide them upon exit:

Private Sub Worksheet_Deactivate()
    Me.Visible = xlSheetHidden
End Sub

It works fine for the worksheets that don't have spaces or & in the title; but for the ones that do, I get the following error:

Run-time error '9' Subscript out of range

The line that the debug hightlights is:

Sheets(ShtName).Visible = xlSheetVisible

The sheet linking to is hidden, it's supposed to un-hide it while you're in it.

 

[Fluff]

Try

        ShtName = Replace(Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1), "'", "")

 

[Outdoorsman80]

Try

        ShtName = Replace(Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1), "'", "")

Thank you, I can confirm it works with spaces and &. I would not have come up with that.

 

[Fluff]

You're welcome & thanks for the feedback.