Hyperlink linking ot the wrong worksheet

Question

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

<Moved from Office on Windows 7>

Answer 1

I have looked at this thread several times and without details of the HyperLink and your workbook it is difficult, if not impossible, to test.

However, some advice with code that is in the worksheet's module.

Because you are changing the ActiveSheet (Selecting another worksheet) in the code then this might be your problem because when the ActiveSheet is changed in code from within a module attached to a worksheet, Excel becomes confused and the end results are indeterminable.

If it is necessary to select other worksheets then it is best to place the code in a standard module and call it from the worksheet module and pass the required variables to it like the following example. Note: This code is untested and simply to give you the idea of how to code when changing the ActiveSheet from code within a worksheet module.

'This event code goes in the worksheet's module

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    'Pass the SubAddress to the called sub

    Call TestCode(Target.SubAddress)

End Sub

'The following code goes in a standard module

Sub TestCode(strLinkTo As String)

     WhereBang = InStr(1, strLinkTo, "!")

     If WhereBang > 0 Then

         priorsampleprojects = Left(strLinkTo, WhereBang - 1)

         Worksheets("priorsampleprojects").Visible = True

         Worksheets("priorsampleprojects").Select

         MyAddr = Mid(strLinkTo, WhereBang + 1)

        Worksheets("priorsampleprojects").Range(MyAddr).Select

     End If

     End    'Ends processing without returning to calling sub

End Sub

You also need similar coding anywhere that you have code in a worksheets module that changes the ActiveSheet (ie. Selects another worksheet.)

Answer 2

Thanks OssieMac,

  it seems the issue was that I had the code in the wrong module. By moving it to the standard worksheet it now works great.

Thanks for The help, Gene