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!

 

[Yongle]

Try this to see if it extracts the sheet name from your hyperlink
Amend cell reference to one that contains a link

Place in SHEET module

Sub WhatIsTheSheet()
    MsgBox GetSheetName(Range("[COLOR=#ff0000]A1[/COLOR]"))
End Sub
Private Function GetSheetName(LinkCell)
    Dim hl As Hyperlink, r As Range
    Set hl = ActiveSheet.Hyperlinks(1)
    Set r = Application.Evaluate(hl.SubAddress)
    GetSheetName = r.Parent.Name
End Function

 

[LuisFM]

Thank you for the input.

It's not working, and I found out something.

I've pointed to cell C2, that has the HYPERLINK formula from the first post.

I've set a watch on your GetSheetName sub and found that when:

Set r = Application.evaluate (hl.SubAddress)

hl=
: Address : "https://vendapef-my.sharepoint.com/personal/luis_moreira_vendapef_com/Documents/Mapa/Revisao/GE16.xlsx" : String : Folha15.GetSheetName

Moving the Range to "C3" (points to GE5U) does not alter this value, I've also created another hyperlink in a "virgin" cell, "I3", no impact.

Any ideas?

Before using the Hyperlink command, I cleared the previous hyperlink.

Note: This was a master file, it had hyperlinks to other files, however, external links are very troublesome for online sharing, so this idea came up - move all the external files to sheets, but hide them so main file is not too cluttered.

 

[Yongle]

Try this and tell me what is printed to the immediate window

Sub WhatsTheAddress()
    MsgBox GetAddressString(Range("C2"))
End Sub

 Private Function GetAddressString(LinkCell As Range)
    Dim hl As Hyperlink
    For Each hl In LinkCell.Parent.Hyperlinks
        If hl.Range.Address = LinkCell.Address Then
            [COLOR=#ff0000]Debug.Print hl.SubAddress[/COLOR]
            GetAddressString = hl.SubAddress
            Exit Function
        End If
    Next hl
End Function

 

[LuisFM]

Again thank you for your help.

Nothing, a bank window popup.
title = Microsoft Excel

OK button. That's it.

hl SubAddress is empty "".

 

[Yongle]

It is ok - I am an idiot
- you said the cell has a formula which I blithely ignored
Watch this space....

 

[LuisFM]

No need for head banging on the wall, the wall has done nothing! =P

Yes, it has a formula, and its different from the other I've encountered in other examples. The HYPERLINK formula is meant to take me to the first available cell on row B (the +8 is because there are some free cells on the top part of the sheet) thus aligning the active cell with data input and saving time.

Maybe I'm looking at this wrong, and should work out a different way to built my hyperlinks so I can get the sheet name more easily?

 

[LuisFM]

OK. More experiments and new data:

I've inserted a Hyperlink to "Sheet1!A1" using "ctrl + K".

Private Sub Worksheet_FollowHyperlink(ByVal target As Hyperlink)
Dim hl As Hyperlink
Dim srtg As String
Application.ScreenUpdating = False
srtg = target.Address
Application.ScreenUpdating = True

End Sub

The code I used was senseless, but all I wanted to do was to monitor the "TARGET" value.

When I use the Hyperlink to "Sheet1!A1" the code "FollowHyperlink" is activated and "target" gets a SubAdress as "Sheet1!A1".

When I click the "C2" cell, with the HYPERLINK formula pointing to "#GE2U!B52" the "FollowHyperlink" is not activated and "Target" remains out of context.

So a Hyperlink set by formula "Hyperlink" is different from one set using "ctrl + k" combo? Why does one trigger "FollowHyperlink" and the other does not?

Again thank you for any help!

 

[Yongle]

Going back to post1...

Try

Sub SheetName()
    Dim LinkCell As Range
    Set LinkCell = Range("C2")
    MsgBox (Mid(LinkCell.Formula, 17, InStr(LinkCell.Formula, "!") - 17))
End Sub

To also cope with spaces in sheet names

MsgBox Replace(Mid(LinkCell.Formula, 17, InStr(LinkCell.Formula, "!") - 17), "'", "")

 

[LuisFM]

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