Podcast #1729 - Hyperlink Hidden Pages

[Cristinky420]

Hey

I have followed all the coding for the podcast 1729 but am getting an error:

Runtime Error #9
Subscript out of range


using the following code

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
LinkTo = Target.SubAddress
WhereBang = InStr(1, LinkTo, "!")
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


Thanks for your help!

 

[Yongle]

Is MySheet the name of the sheet?

If so..

Worksheets([COLOR=#ff0000][B]"[/B][/COLOR]MySheet[COLOR=#ff0000][B]"[/B][/COLOR]).Visible = True

For future posts...
The # icon above reply window adds code tags to your post.
Code is easier to read if code tags are used

[ CODE ] paste code here [ /CODE ]

 

[Joe4]

Yongle,
It is a string variable. It is defined in the line right above where they are using it.

[COLOR=#333333]MySheet = Left(LinkTo, WhereBang - 1)[/COLOR]

Cristinky420,
Are you referring to a MrExcel Podcast, or some other podcast?

 

[Yongle]

@Joe4 - I am glad one of us was awake

 

[Fluff]

What is the name of the sheet that you are linking too?
If the sheet name has a space, is numerical, or is a valid cell reference, then the sheet name will be wrapped in single quotes which will cause the error.

 

[Yongle]

@Cristinky420

This happens on Excel 365...
Another way to make the code fail on that same line is to change the name of the linkTo Sheet AFTER creating the link
- you can easily test to see if this has happened by hovering over the link and checking if the sheet name in the little pop-up matches the actual name of the sheet

 

[Cristinky420]

I'm referring to the MrExcel podcast #1729

This is how the code reads on "Master"

[ CODE ] Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
LinkTo = Target.SubAddress
WhereBang = InStr(1, LinkTo, "!")
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 [ /CODE ]

"MySheet" shows as '0126' like te sheet is numbered and the link leads to when the sheet is unhidden. Using Excel 2016.

 

[Fluff]

Try

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

 

[Cristinky420]

Thank you very much! This worked!

 

[Fluff]

You're welcome & thanks for the feedback