Select Worksheet and Hide Current Worksheet using Hyperlink

[DKrout2018]

I have a workbook that contains 3 worksheets:

1. Table of Contents ("TOC")
2. Budget Summary ("Summary")
3. Project Details ("Project")

I want to use hyperlinks/VBA code to select/hide worksheets from the Table of Contents worksheet. So the only worksheet that I want to be visible in the workbook is the TOC Worksheet. And use the hyperlinks on the TOC worksheet to navigate to the other/hidden worksheets.

How can I include a hyperlink on the "Summary" and "Project" worksheets that will hide that worksheet and go back to the TOC worksheet?

 

[DKrout2018]

Is there a trick to getting help on the forum? I've seen posts responded to within minutes but I'm not sure if that's because of a user's "status" or buzz words within the thread. Any help would be much appreciated. hanks!

 

[Jaafar Tribak]

Have you tried adding an hyperlink on the "Summary" and "Project" worksheets via the excel user intrface ie:

Right-click on a cell on the worksheet>select "Hyperlink.." from the context menu>select the tab that says 'Place in this Document"> choose the "TOC" worksheet from the list and press OK.

Now, clicking on the newly added hyperlink should take you back to the 'TOC' worksheet.

 

[My Aswer Is This]

Hyperlinks can take you to a sheet but Hyperlinks cannot hide and unhide sheets.
You would need Vba script to do this.
We can write you a script to do this if you want.

Is there a trick to getting help on the forum? I've seen posts responded to within minutes but I'm not sure if that's because of a user's "status" or buzz words within the thread. Any help would be much appreciated. hanks!

 

[My Aswer Is This]

If your willing to use a Vba script you have two choices.

Click on a button to run the script.
Or double click a cell to run the script.

If you want to double click a cell to run the script tell me what cell on each sheet you want to double click on to run the script.

On your Master sheet you would have to provide more the one cell to double click on.

If you double clicked on a cell with the value "One" on the Master sheet the script would unhide the sheet named "One" and take you to the sheet named "One"

Now on the other sheets you could double click on some cell and the script would hide the active sheet and then take you to the Master sheet.

 

[Jaafar Tribak]

You could use the SheetFollowHyperlink workbook event to show/hide the worksheets when clicking the hyperlinks.

Assuming that the workbook constains 3 sheets "TOC","Summary" and "Project", the code below will add the hyperlinks as follows:

Sheet 'TOC' Cell A1 hyperlink to "Summary" Sheet
Sheet 'TOC' Cell A2 hyperlink to "Project" Sheet

Sheet 'Summary' Cell A1 hyperlink to "TOC" Sheet
Sheet 'Project' Cell A1 hyperlink to "TOC" Sheet

The code will also hide all sheets except sheet "TOC".

The code will take effect when executing the Workbook_Open event code.

Option Explicit

Private Sub Workbook_Open()
    Dim ws As Worksheet
    
    With Worksheets("TOC")
        .Hyperlinks.Add Anchor:=.Range("a1"), Address:="", SubAddress:="Summary!A1", TextToDisplay:="Summary!A1"
        .Hyperlinks.Add Anchor:=.Range("a2"), Address:="", SubAddress:="Project!A1", TextToDisplay:="Project!A1"
        .Visible = xlSheetVisible
    End With
    
    For Each ws In Me.Worksheets
        With ws
            If ws.Name <> "TOC" Then
                .Hyperlinks.Add Anchor:=.Range("a1"), Address:="", SubAddress:="TOC!A1", TextToDisplay:="TOC!A1"
                .Visible = xlSheetHidden
            End If
        End With
    Next
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Worksheets("TOC").Visible = xlSheetVisible
    Worksheets("Summary").Visible = xlSheetHidden
    Worksheets("Project").Visible = xlSheetHidden
End Sub

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    Sheets(Range(Target.Parent).Parent.Name).Visible = True
    Sheets(Range(Target.Parent).Parent.Name).Activate
    Sh.Visible = xlSheetHidden
End Sub

 

[DKrout2018]

So appreciate the help Jaafar. I do have a few other worksheets in the workbook that I do not want to hide. So let's use the following example:

1. Worksheet "TOC" - I want to unhide Worksheet "Summary" below by using a hyperlink in Cells A1:C1 on this page
2. Worksheet "Summary" - I want to have 2 hyperlinks on this page
a. First Hyperlink (in Cells A1:C1) - I want to go back to the "TOC" Worksheet and hide this "Summary" Worksheet
b. Second Hyperlink (in Cells A2:C2) - I want to unhide and go to Worksheet "Details" (while leaving the "Summary" Worksheet open/unhidden)
3. Worksheet "Details" I want to have 1 hyperlink (in Cells A1:C1) that will take me back to the "Summary" worksheet and hide this "Details" worksheet

This allows me to keep the workbook cleaner and navigate via the "TOC" Worksheet for the most part. (Exception: I only want to get to the "Details" worksheet through the "Summary" Worksheet)

I hope this makes it a little clearer

 

[Jaafar Tribak]

What do you mean by "hyperlink in Cells A1:C1" .. An hyperlink is added to a single cell not to a group of cells unless you want to duplicate the hyperlink on each cell or unless the range A1:C1 is a merged range.

Can you clarify the above ?

 

[My Aswer Is This]

I have the same sort of questions. I would think it would be better for the user to explain why he needs links in three cells like: (A1:C1)

And also why can we not just use sheet double click events instead of using Hyperlinks and vba solutions.

I have been monitoring this thread but not giving advice on clicking on Hyperlinks to go to sheets and then needing vba to open and close sheets. A cell double click event could take you to another sheet and hide or unhide sheets all at once not requiring Hyperlinks. Just double click on a cell and presto you get what you want.

 

[DKrout2018]

The hyperlink spans several cells solely for aesthetic reasons. And they are consistent across all the worksheets - they are used to navigate around the workbook so there's really only the one TOC Worksheet visible

If there are other (better) solutions I'm all for it. I jus inherited this workbook so was trying to fix the broken code that was already there