VBA to link to hidden sheets

[newbie188]

Hi, complete newb. I was looking to hide sheets that are linked from a Title Sheet. I followed the code from

but I'm getting an error, as highlighted below. I assumed I would need to change something from the code in the video but can't see what. should (MySheet) actually be the name of a specific sheet? The video was excellent but I can't get the code to work.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    LinkTo = Target.SubAddress
    WhereBang = InStr(1, LinkTo, "!")
    If WhereBang > 0 Then
        MySheet = Left(LinkTo, WhereBang - 1)
        [COLOR=rgb(226, 80, 65)]Worksheets(MySheet).Visible = True[/COLOR]
        Worksheets(MySheet).Select
        MyAddr = Mid(LinkTo, WhereBang + 1)
       
        Worksheets(MySheet).Range(MyAddr).Select
    End If
 
 

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

        MySheet = Replace(Left(LinkTo, WhereBang - 1), "'", "")

 

[newbie188]

Hi & welcome to MrExcel.
How about

        MySheet = Replace(Left(LinkTo, WhereBang - 1), "'", "")

Thanks so much for the reply. I made the suggested amendment but no luck

 

[Fluff]

What error do you get & what is the name of the sheet?

 

[newbie188]

What error do you get & what is the name of the sheet?

I went into the workbook for the first time in a few weeks and it sort of works now?! I say 'sort of' because the individual sheets are hidden but when I click on the links for the sheets, it unhides the sheets and they remain unhidden after that. maybe that's the way it's supposed to be?

 

[Fluff]

That's the way it works, if you want the sheets to be hidden when you leave them, put this in the sheet module for each hidden sheet

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

 

[Outdoorsman80]

This script works great if the Worksheet does not have spaces or special characters. Is there a way around this?

Thank you!

 

[Fluff]

Please start a thread of your own, showing the code that you are using. Thanks

 

[Outdoorsman80]

It's the exact code from this thread... I can do that if you'd like.

 

[Fluff]

Yes please.