Hyperlink to Hidden Sheets

[Charisa_L]

Hi there

I have a worksheet with 7 Sheets. The first sheet is a summary and I’ve created hyperlinks to the other 6 sheets for a person to access from the main page. [Sheet names: Sheet1 (Summary), Sheet2 (100% Complete), Sheet3 (Almost Complete), Sheet4 (One Piece), Sheet5 (Two Pieces), Sheet 6 (Three Pieces), Sheet 7 (Have Nothing)]

I want to hide all sheets except for the first one, which I can do and works fine. But from there, I still want the hyperlinks to work. I need the hyperlinks to open(unhide) the hidden page and then close(hide) it again when I click on the “back to Summary” hyperlink on each of the hidden pages.

Is this possible? Will you please tell me how to do it?

Thank you
Charisa

 

[Lewiy]

Here's one way, this assumes that your list of hyperlinks are in column A, but you can change to the approriate cell references. This will negate the need for an actual hyperlink as it replaces the functionality. The code triggers when you double click the cell.

Private Sub Worksheet_Activate()
    Dim sh As Worksheet
    
    For Each sh In ThisWorkbook.Sheets
        If sh.Name <> "Summary" Then
            sh.Visible = xlSheetHidden
        End If
    Next sh
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Select Case Target.Address
        Case "$A$1"
            Sheets("100% Complete").Visible = True
            Sheets("100% Complete").Activate
        Case "$A$2"
            Sheets("Almost Complete").Visible = True
            Sheets("Almost Complete").Activate
    End Select
End Sub

 

[CharisaL]

Thank you so much. That works 100%!

 

[Charisa_L]

Hi Lewiy

I have one more problem. When I save and close my file and reopen it, it doesn't work anymore. At first I saved it as a normal excel file, which obviously doesn't save the VBA stuff. Then I saved it as a macro enabled excel file. When I reopen it, the code is saved in the file, but it doesn't work. I've tried taking it out and putting it back in, but it still doesn't work. Any idea what I can do?

 

[pjeterd]

Thank you so much. That works 100%!

This doesn't work form me (i'm using office 2007)...

Please help!

 

[grizz]

I created a drop down in A2 of my worksheets and used this

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2")) Is Nothing Then
    Sheets(Range("A2").Text).Activate
End If
End Sub

then go to tools / options / and remove check mark from show tabs

 

[pjeterd]

Since none of codes given above worked, I've been searching in some other forums, and finally got an answer, and yes it worked:

Code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
   
   Dim strSheet As String
    
   If InStr(Target.Parent, "!") > 0 Then
      strSheet = Left(Target.Parent, InStr(1, Target.Parent, "!") - 1)
   Else
      strSheet = Target.Parent
   End If
   
   Sheets(strSheet).Visible = True
   Sheets(strSheet).Select
   
End Sub
Private Sub Worksheet_Activate()
   
   Dim ws As Excel.Worksheet
   
   For Each ws In ThisWorkbook.Worksheets
      ws.Visible = (ws.Name = Me.Name)
   Next
   
End Sub

 

[rgupta47]

PJeter, can you highlight which fields should be replaced for your code to work?

 

[Infiniti]

Sorry for necro-ing this but what parts do people need to edit in order for the code to work?
Sheet name ect..

 

[dzaak]

Hi All,

I've been trying to figure this out too. So I tried the original code proposed, and works perfectly. These are the things you need to change from the original code.

Code:
Private Sub Worksheet_Activate()
Dim sh As Worksheet

For Each sh In ThisWorkbook.Sheets
If sh.Name <> "Summary" Then
sh.Visible = xlSheetHidden
End If
Next sh
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case Target.Address
Case "$A$1"
Sheets("100% Complete").Visible = True
Sheets("100% Complete").Activate
Case "$A$2"
Sheets("Almost Complete").Visible = True
Sheets("Almost Complete").Activate
End Select
End Sub

I've bolded what needs to be changed.
Case A$1$ and Case A$2$ = target cell to be double clicked (this is where your link is).
100% Complete or Almost complete = The name of the worksheet (tab).

If you want more than 2 links, simply copy and paste a case below the rest, and adjust the target cell and name of worksheet as required.

I'm no good at VBA, so I've got my own question which might sound stupid to the VBA experts here.

Regarding the "Summary" sheet that is not to be hidden. How do I adjust the code so multiple sheets are not hidden, not just the one Summary Sheet?

Thanks.