If then hyperlink to hidden sheet

[ebersteve]

I have some hyperlinks that go to hidden sheets on my workbook without issue. But I have a if the statement that works when the page I am hyperlinking to is not hidden. I would like it to work when it is hidden.

the if then statement is
=IF(EXACT(B7,"Vanco"),HYPERLINK("#Vanco!B2","Click for Alternate Vanco Sheet"),"")

My Current VBA which allows my other links to work

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

LinkTo = Target.SubAddres
WhereBang = InStr(1,LinkTo, "!")
If whereBang>0 Then
MySheet = Left(LinkTo, WhereBang -1)
Worksheet(MySheet).Visible=True
Worksheet(MySheet).Select
MyAddr=Mid(LinkTo,WhereBang+1)
Worksheets(MySheet).Range(MyAddr).Select
End If

End Sub

 

[DanteAmor]

You have some details in your code, I put them in blue:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    LinkTo = Target.SubAddres[COLOR=#0000ff]s[/COLOR]
    WhereBang = InStr(1, LinkTo, "!")
    If WhereBang > 0 Then
        MySheet = Left(LinkTo, WhereBang - 1)
        Worksheet[COLOR=#0000ff]s[/COLOR](MySheet).Visible = True
        Worksheet[COLOR=#0000ff]s[/COLOR](MySheet).Select
        MyAddr = Mid(LinkTo, WhereBang + 1)
        Worksheets(MySheet).Range(MyAddr).Select
    End If
End Sub

The hyperlink that you have as a result of a formula, does not activate the FollowHyperlink event, you have to use the Worksheet_SelectionChange event, an example of how it could be.
Add the following code in the events of your sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
     
    If Target.Value = "Click for Alternate Vanco Sheet" Then
        wformula = Target.Formula
        ini = InStr(1, wformula, "#") + 1
        fin = InStr(1, wformula, "!")
        MySheet = Mid(wformula, ini, fin - ini)
        Worksheets(MySheet).Visible = True
        Worksheets(MySheet).Select
        MyAddr = Mid(wformula, fin + 1, 2)
        Worksheets(MySheet).Range(MyAddr).Select
    End If
End Sub

Regards

 

[ebersteve]

I repaced the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
     
    If Target.Value = "Click for Alternate Vanco Sheet" Then
        wformula = Target.Formula
        ini = InStr(1, wformula, "#") + 1
        fin = InStr(1, wformula, "!")
        MySheet = Mid(wformula, ini, fin - ini)
        Worksheets(MySheet).Visible = True
        Worksheets(MySheet).Select
        MyAddr = Mid(wformula, fin + 1, 2)
        Worksheets(MySheet).Range(MyAddr).Select
    End If
End Sub

This did not work. The links no longer work nor does the IF Then Hyperlink. Did I miss something? Any other ideas?

 

[DanteAmor]

You have to put all the 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
'
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "Click for Alternate Vanco Sheet" Then
        wformula = Target.Formula
        ini = InStr(1, wformula, "#") + 1
        fin = InStr(1, wformula, "!")
        MySheet = Mid(wformula, ini, fin - ini)
        Worksheets(MySheet).Visible = True
        Worksheets(MySheet).Select
        MyAddr = Mid(wformula, fin + 1, 2)
        Worksheets(MySheet).Range(MyAddr).Select
    End If
End Sub

Now, select a cell with the result of the formula: "Click for Alternate Vanco Sheet"

 

[ebersteve]

THANK YOU for your quick response and help! I put in all the code and Selected the cell for the alternate vanco sheet but does not do anything while that page is hidden. The other links work

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
'
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "Click for Alternate Vanco Sheet" Then
        wformula = Target.Formula
        ini = InStr(1, wformula, "#") + 1
        fin = InStr(1, wformula, "!")
        MySheet = Mid(wformula, ini, fin - ini)
        Worksheets(MySheet).Visible = True
        Worksheets(MySheet).Select
        MyAddr = Mid(wformula, fin + 1, 2)
        Worksheets(MySheet).Range(MyAddr).Select
    End If
End Sub

 

[DanteAmor]

To work the selected cell must have the text: Click for Alternate Vanco Sheet and that text must be the result of your formula
=IF(EXACT(B7,"Vanco"),HYPERLINK("#Vanco!B2","Click for Alternate Vanco Sheet"),"")

 

[ebersteve]

To work the selected cell must have the text: Click for Alternate Vanco Sheet and that text must be the result of your formula
=IF(EXACT(B7,"Vanco"),HYPERLINK("#Vanco!B2","Click for Alternate Vanco Sheet"),"")

 

[ebersteve]

I tried to post a picture but that did not work. I seem to be doing everything you recommended but it still does not work.

 

[ebersteve]

I made a new workbook with these codes and it works so I am not sure why my current workbook does not?

 

[DanteAmor]

Maybe the sheet has some error. That's why in your new book it works.
It's good that it works!
Regards Dante Amor