Hyperlink to open hidden worksheets

[majedix]

I have a workbook with many work sheets. My main sheet (called Last 10 days reports) has links to other sheets.
I would like to hide all sheets except the main sheet (Last 10 days reports) when the user opens the file. When the user clicks on any hyperlink to any worksheet.

Main Sheet (Last 10 days reports)
Sheets to be hidden (Sept13 report) and sheet (Sept14 report )and so on..


Best Regards,

 

[wigi]

Hello

Here's custom coding:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ActiveWorkbook.Sheets(Split(Target.SubAddress, "!")(0))
    ws.Visible = 1
    Application.EnableEvents = 0
    Target.Follow
    Application.EnableEvents = 1
End Sub

It works when the hyperlink (Ctrl-K) refers to a cell in a different sheet.

 

[wigi]

This more generic code allows you to also jump to a named range on a hidden sheet:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    Dim ws As Worksheet
    Dim nm As Name
    
    On Error Resume Next
    If Target.Type = 0 Then
        sq = Split(Target.SubAddress, "!")
        If UBound(sq) = 1 Then
            Set ws = ActiveWorkbook.Sheets(sq(0))
        Else
            Set nm = ActiveWorkbook.Names(Target.SubAddress)
            Set ws = ActiveWorkbook.Sheets(Split(Mid(nm, 2), "!")(0))
        End If
        ws.Visible = 1
    End If
    
    Application.EnableEvents = 0
    Target.Follow
    Application.EnableEvents = 1
    
End Sub

 

[majedix]

Hello

Here's custom coding:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ActiveWorkbook.Sheets(Split(Target.SubAddress, "!")(0))
    ws.Visible = 1
    Application.EnableEvents = 0
    Target.Follow
    Application.EnableEvents = 1
End Sub

It works when the hyperlink (Ctrl-K) refers to a cell in a different sheet.

This more generic code allows you to also jump to a named range on a hidden sheet:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    Dim ws As Worksheet
    Dim nm As Name
    
    On Error Resume Next
    If Target.Type = 0 Then
        sq = Split(Target.SubAddress, "!")
        If UBound(sq) = 1 Then
            Set ws = ActiveWorkbook.Sheets(sq(0))
        Else
            Set nm = ActiveWorkbook.Names(Target.SubAddress)
            Set ws = ActiveWorkbook.Sheets(Split(Mid(nm, 2), "!")(0))
        End If
        ws.Visible = 1
    End If
    
    Application.EnableEvents = 0
    Target.Follow
    Application.EnableEvents = 1
    
End Sub

Thanks for your replay Wigi but acually i have incerted the above coded but nothing happen when i click on it there no action.

best regards to you.

 

[wigi]

Where did you paste the code?
What kind of hyperlinks do you have?
And do the hyperlinks target a particular cell or a named range?

 

[majedix]

Where did you paste the code?
What kind of hyperlinks do you have?
And do the hyperlinks target a particular cell or a named range?

1- i paste it in my main sheet (last 10 days report) view code and i past it there.
2- the hyperlinks that i have is shaps in cells and once i click the chaps it goes me into sheets ( its work well but when i hide the sheets it desont work at all)
3- Not sure but the target is textbox in the sheets only in this sheet the text box.


Thanks in advance

 

[wigi]

The event "Private Sub Worksheet_FollowHyperlink" (see my earlier code) does not fire when you use a Shape. Nor would it fire if you use the HYPERLINK function in Excel.
Either you use normal hyperlinks (hit Ctrl-K) and the code works, either you assign a macro procedure to each shape. That procedure should contain the code to make the sheet Visible = 1.

Please can you rephrase this so that it becomes clear to the persons trying to read it:

3- Not sure but the target is textbox in the sheets only in this sheet the text box.

Thanks in advance.

 

[majedix]

The event "Private Sub Worksheet_FollowHyperlink" (see my earlier code) does not fire when you use a Shape. Nor would it fire if you use the HYPERLINK function in Excel.
Either you use normal hyperlinks (hit Ctrl-K) and the code works, either you assign a macro procedure to each shape. That procedure should contain the code to make the sheet Visible = 1.

Please can you rephrase this so that it becomes clear to the persons trying to read it:



Thanks in advance.

Thanks for quick replay, i mean the target is a sheet contains ( TextBox ).

 

[wigi]

Please be precise. How can you link to a textbox? Or do you mean a cell?

 

[majedix]

Please be precise. How can you link to a textbox? Or do you mean a cell?

yes to a cell,

Thanks.