Hyperlink linking ot the wrong worksheet

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>

 

Question Info


Last updated August 22, 2018 Views 401 Applies to:
Answer
Answer

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.)


Regards,

OssieMac

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.