Hyperlink linking ot the wrong worksheet

[ctigers]

Have an unusual problem that I can't figure out with hyperlinks. I have a main page (a directory for all the sheets) which is hyperlinked to numerous sheets and all have hyperlinks to take me back to the main page. My problem is on one sheet I also have a hyperlink with a vba to unhide a sheet for viewing and a vba on the hidden sheet to re-hide it and take me back to the sub-sheet (if that makes sense). My problem is when I click on the hyperlink on this sub-sheet to take me back to the main sheet it keeps going to the hidden sheet! My code on the sub-sheet to unhide the one sheet is:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
LinkTo = Target.SubAddress
WhereBang = InStr(1, LinkTo, "!")
If WhereBang > 0 Then
priorsampleprojects = Left(LinkTo, WhereBang - 1)
Worksheets("priorsampleprojects").Visible = True
Worksheets("priorsampleprojects").Select
MyAddr = Mid(LinkTo, WhereBang + 1)
Worksheets("priorsampleprojects").Range(MyAddr).Select
End If

End Sub


The code on the hidden sheet is:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Worksheets("memosamples").Select
Target.Parent.Worksheet.Visible = False

End Sub

Why can't I get the non vba hyperlink to take me to the main sheet?

Thanks Gene

 

[jsotola]

put this code on your subsheet (added a stop command)

and click link to main sheet

you will see why it does not do what you wish

you probably need to check Target name and change program flow accordingly

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

[COLOR=#ff0000]Stop    ' for debugging ...  press F8 to single step through code (press number of times)

        ' when code is stopped ... right click on the word "Target" (any of them) ... add watch ... ok
        ' look at the value of Target in the "watches" window
[/COLOR]
      [COLOR=#ff0000]  ' you can also hover the mouse pointer over variables and see a tooltip pop up[/COLOR]

    LinkTo = Target.SubAddress
    WhereBang = InStr(1, LinkTo, "!")

    If WhereBang > 0 Then
        priorsampleprojects = Left(LinkTo, WhereBang - 1)
        Worksheets("priorsampleprojects").Visible = True
        Worksheets("priorsampleprojects").Select
        MyAddr = Mid(LinkTo, WhereBang + 1)
        Worksheets("priorsampleprojects").Range(MyAddr).Select
End If

End Sub

 

[ctigers]

Works!! Thanks for the info, can use it on many other sheets I have. Gene