Error on "Hyperlink to a Hidden Worksheet" - Podcast #1729

[esc5972]

Good Morning,

I have followed step by step the above tutorial but am getting an error. The code that I typed is the following:

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

The error that I am getting is on the line where the fonts are in blue. I would really like to have this working since I am working on a big project and require that the sheets are only accessible through a hyperlink and are hidden at all times unless the particular sheet is being used.

Thanking you in advance,

Emanuel (Sam) Camilleri

 

[baitmaster]

Welcome to the board. When the code breaks, what is the value of "MySheet"? You can find this hovering over it with the mouse (whilst in break mode), or by typing "?MySheet" in the Immediate Window (VB Editor > View > Immediate Window) then hitting Enter

 

[baitmaster]

Another way to accurately test the text string being created, including testing for hidden space characters, is to add the following code line above your blue line

debug.print "[" & mysheet & "]"

I'd also recommend - though I don't think it will help in this case - ensuring you declare all your variables properly, and using a standardised notation so you can tell what type of variables they are. For example I'd use

dim str[COLOR=#574123]MySheet as string[/COLOR]

Wherever I see this variable name I automatically know that it's a text string. If I try to use it incorrectly, VB will give me a meaningful error message

 

[esc5972]

Welcome to the board. When the code breaks, what is the value of "MySheet"? You can find this hovering over it with the mouse (whilst in break mode), or by typing "?MySheet" in the Immediate Window (VB Editor > View > Immediate Window) then hitting Enter

I think I just found the answer. I have just renamed a couple of sheets as per example and it actually worked. So what I am going to do is rename all my sheets without having any blank spaces in between.

 

[John_w]

No need to remove blank spaces in the sheet names. Just change the MySheet = line to:

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

Also, instead of the Worksheet_FollowHyperlink in the Menu sheet and the multiple Worksheet_FollowHyperlink in all the other sheets to return to the Menu sheet, you can do everything in a single event handler in the ThisWorkbook module:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

    Dim LinkTo As String, WhereBang As Long, MySheet As String, MyAddr As String
    
    If Sh.Name = "Menu" Then
    
        LinkTo = Target.SubAddress
        WhereBang = InStr(1, LinkTo, "!")
        If WhereBang > 0 Then
            MySheet = Replace(Left(LinkTo, WhereBang - 1), "'", "")
            Worksheets(MySheet).Visible = True
            Worksheets(MySheet).Select
            MyAddr = Mid(LinkTo, WhereBang + 1)
            Worksheets(MySheet).Range(MyAddr).Select
        End If
   
    Else
    
        Target.Parent.Worksheet.Visible = False
        Worksheets("Menu").Range("A1").Select
    
    End If
    
End Sub

And delete the Worksheet_FollowHyperlink code in the sheet modules.

 

[esc5972]

No need to remove blank spaces in the sheet names. Just change the MySheet = line to:

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

Also, instead of the Worksheet_FollowHyperlink in the Menu sheet and the multiple Worksheet_FollowHyperlink in all the other sheets to return to the Menu sheet, you can do everything in a single event handler in the ThisWorkbook module:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

    Dim LinkTo As String, WhereBang As Long, MySheet As String, MyAddr As String
    
    If Sh.Name = "Menu" Then
    
        LinkTo = Target.SubAddress
        WhereBang = InStr(1, LinkTo, "!")
        If WhereBang > 0 Then
            MySheet = Replace(Left(LinkTo, WhereBang - 1), "'", "")
            Worksheets(MySheet).Visible = True
            Worksheets(MySheet).Select
            MyAddr = Mid(LinkTo, WhereBang + 1)
            Worksheets(MySheet).Range(MyAddr).Select
        End If
   
    Else
    
        Target.Parent.Worksheet.Visible = False
        Worksheets("Menu").Range("A1").Select
    
    End If
    
End Sub

And delete the Worksheet_FollowHyperlink code in the sheet modules.

Worked like a charm

Thanks for all your help