VBA code to hyperlink cell range in hidden tab

[Tiago Pinto]

hello,

i´ve used the code in thread VBA to link to hidden sheets .

it works great, the only problem is that it always open the hidden tab in the same cell (A1). I need the code to open the hidden tab in the cell range I put in the hyperlink.

thanks in advance!

 

[NateSC]

If you right click and edit the hyperlink, it should show "Text to display" and "Type the cell reference". Is your desired cell in the "Type the cell reference" box? If so, the code should work fine. If the cell reference is only in the "Text to display" then it is just non-functional text.

In the code, MyAddr = Mid(LinkTo, WhereBang + 1) is what sets the cell it is going to select on the target sheet. It is pulling this directly from the hyperlink. You can overwrite this in the code by changing MyAddr = Mid(LinkTo, WhereBang + 1) to something like MyAddr = "C5", but it will be completely divorced from the hyperlink at that point and would apply to any hyperlink you click on that sheet.

Does this help?

 

[Tiago Pinto]

hello NateSC,

thanks for the quick reply! the desire cell is in the "cell reference" but it does not work, it only opens the hidden tab in "A1" or, if I scroll down in the hidden tab and click "back button" to hide the tab and return to main tab, next time I click a hyperlink it will open the hidden tab where i left.

The cell reference actually is a range like "A445:A479" because is the height of the picture I have in the hidden tab. the next hyperlink is for a picture in the same tab with "cell reference" "A504:A564".

I would like to add a line in the VBA code to pick the cell range in the right hand side of "!" in the hyperlink text.

thanks

 

[NateSC]

MyAddr = Mid(LinkTo, WhereBang + 1) pulls everything to the right of the "!". It could be an Excel version issue, but I tried adding a range to my hyperlink and it was working fine. Version differences could explain why you and I are seeing different results.

If you debug or step through your code, what value is your program assigning to MyAddr?

 

[Tiago Pinto]

Hello Again,

The VBA code I have is actually this one:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim ShtName As String
ShtName = Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1)
Sheets(ShtName).Visible = xlSheetVisible
Sheets(ShtName).Select
End Sub

 

[NateSC]

Ahhh. No problem. So when you do the Sheets(ShtName).Select, you just tell it to go to the sheet and not a specific cell.

Try this:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim ShtName As String
    ShtName = Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1)
    Sheets(ShtName).Visible = xlSheetVisible
    ' Grab the range from the Hyperlink
    RngName = Mid(Target.SubAddress, InStr(1, Target.SubAddress, "!") + 1)
    ' Degine sheet and range to select
    Sheets(ShtName).Range(RngName).Select
End Sub

 

[Tiago Pinto]

ok, so I paste the code and now the first time I click a hyperlink I get a "run-time error 1004 - select method of range class failed". Debug highlights this line "Sheets(ShtName).Range(RngName).Select"

strange thing is after first click, the links work fine, it's only on first time

thank you

 

[NateSC]

Oops. Should have left in the sheets.select line. Have to select the sheet and then select the range in the next line.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim ShtName As String
    ShtName = Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1)
    Sheets(ShtName).Visible = xlSheetVisible
    ' Grab the range from the Hyperlink
    RngName = Mid(Target.SubAddress, InStr(1, Target.SubAddress, "!") + 1)
    ' Define sheet and range to select
    Sheets(ShtName).Select  ' <----------------------------------------------------------------- ADD THIS BACK IN
    Sheets(ShtName).Range(RngName).Select
End Sub