hyperlink to hidden sheet

[LuisFM]

Hi there!

First off: Yes! I did do a lot of reading before posting here! I've found many solved threads to similar situations but I've been unable to implement any of them for my own situation.

My hyperlinks are of the type:

=HYPERLINK("#"&"GE2U!" &"B"&VALUE.NUMBER(COUNT(GE2U!B:B)+8);"2")

(note, there may be spelling errors due to translation - my excel is in PT and I'm translating to EN for this post, but hyperlink works!)

In this case Hyperlink points to #GE2U!B52

Sheet Name is GE2U

My problem, I guess, is in extracting this Name to turn the sheet visible.

Here's one the many codes I've tried in my experiments:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim strSheet As String
    strSheet = Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1)
    If Left(strSheet, 1) = "'" Then
        strSheet = Mid(strSheet, 2, Len(strSheet) - 2)
    End If
    Worksheets(strSheet).Visible = xlSheetVisible
    Application.EnableEvents = False
    Target.Follow
    Application.EnableEvents = True
 End Sub

This was placed in the code for the sheet where the hyperlinks are!

Notes:
all the codes I've looked at had some "pause" mechanism in place, this one has application.enableEvents = false, others had "screenupdating = false";
This in particular has the possibility in it the use "mid" rather than just "left" for the string read, though the " ' " marker seems not relevant for this case;
Sheet name and Sheet Code Name are not the same in most of them, but in this case I've tried changing them both to GE2U - no impact;
I've tried to use stop points in the page code to see what was stored in the strings at the time when the hyperlink was pressed, but the code did not stop there, so I'm wondering it it's even running...

Hiding the sheet once I leave it is accomplished by adding this code to the "deactivate" event on GE2U!:

me.visible = xlsheethidden

Help please!

 

[Jaafar Tribak]

Thank You, it works - using the formula to treat the cell content as text and reading after the # to the !.

Sadly, because it does not trigger the FollowHyperlink event I'm now at a loss as how to implement it.

I'll have to keep thinking on this...!

This may interest you:
https://www.mrexcel.com/forum/excel...idden-sheet-using-cell-hyperlink-formula.html

 

[Yongle]

When a cell containing =HYPERLINK formula is selected, the code below unhides the sheet
Now it is possible to click on the link

Put in sheet modue

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Left(Target.Formula, 10) = "=HYPERLINK" Then
        On Error Resume Next
        Sheets(Replace(Mid(Target.Formula, 17, InStr(Target.Formula, "!") - 17), "'", "")).Visible = xlSheetVisible
    End If
End Sub

Put this in GE2U sheet module to auto-hide that sheet when another tab is clicked

Private Sub Worksheet_Deactivate()
    Me.Visible = xlSheetHidden
End Sub