Hyperlink to Hidden Tabs in Excel

[spruitt]

Hello,
I cannot seem to get this to work when I've searched threads on this..
Basically, i have a table of contents that hyperlinks to over 200 sheets.
So first sheet is called "Table of Contents"
in B3 of 'Table of Contents' sheet, it says "Figure 1.1" and it hyperlinks to A1 of the 'Figure 1.1' sheet. What I want to do, is to be able to hide all of the 200+ sheets, so that only the 'Table of Contents' sheet is visible. Then, when I click on cell B3 ("Figure 1.1"), it unhides that sheet and takes me to A1. I also want there to be a hyperlink in the 'Figure 1.1' sheet that says something like "Click here to return to Table of Contents" and when I click it, it then re-hides the 'Figure 1.1' tab and brings me back to the 'Table of Contents' tab.

I have looked at videos and threads and when I type in exactly what they do, it never works for me. I am a complete newbie to VBA and so if someone could please write the code using MY sheet names and hyperlink names, that would be amazing (also, remember I have over 200 sheets, so if you could do it for say 'Figure 1.2' and 'Figure 1.3' etc. so I know then how to continue the code (if relevant).

I have Excel 2010

THANK YOU!

 

[sericom]

If your hyperlinks match your sheet names then the following should work

Private Sub Worksheet_Activate()
Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If InStr(1, ws.Name, "Table of Contents", vbTextCompare) = 0 Then
            ws.Visible = False
        End If
    Next ws
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
With Worksheets(Target.Range.Value)
    .Visible = True
    .Activate
    .Range("A1").Select
End With
End Sub

Right click your Table of Contents tab and click View Code, paste the above in.

It will hide all worksheets except 'Table of Contents' whenever that sheet is activated, so just place your hyperlinks to link back to Table of Contents, and the sheet will hide when clicked.

 

[spruitt]

If your hyperlinks match your sheet names then the following should work

Private Sub Worksheet_Activate()
Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If InStr(1, ws.Name, "Table of Contents", vbTextCompare) = 0 Then
            ws.Visible = False
        End If
    Next ws
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
With Worksheets(Target.Range.Value)
    .Visible = True
    .Activate
    .Range("A1").Select
End With
End Sub

Right click your Table of Contents tab and click View Code, paste the above in.

It will hide all worksheets except 'Table of Contents' whenever that sheet is activated, so just place your hyperlinks to link back to Table of Contents, and the sheet will hide when clicked.

THANK YOU SO MUCH! worked perfectly

 

[craigexcel]

This is exactly what I need too, but it was unsuccessful for me. I copied / pasted the code into the "Table of Contents" sheet's code window.
The 'return to the Table of Contents sheet' part of the code works great,
..... but ...
the 2nd Sub throws an error, specifically, "Run-time error '9': Subscript out of range". All the non-Table of Contents sheets are hidden, but I see this error message.
Any thoughts why this isn't working??

[edit: I'm largely unfamiliar with really how "Worksheet_FollowHyperlink(ByVal Target As Hyperlink)" works.

sheet names are as follows:
Sheet1 was renamed to "Table of Contents"
Sheet2 = "Sheet2"
Sheet3 = "Sheet3"

There are 2 hyperlinks on the ToC sheet (to Sheet2 and Sheet3), and Sheets 2 & 3 both have a link back to the ToC sheet.]

 

[craigexcel]

... and the error occurs @

Private Sub Worksheet_Activate()
Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If InStr(1, ws.Name, "Table of Contents", vbTextCompare) = 0 Then
            ws.Visible = False
        End If
    Next ws
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
[COLOR=#FF0000]With Worksheets(Target.Range.Value)
[/COLOR]    .Visible = True
    .Activate
    .Range("A1").Select
End With
End Sub

 

[sericom]

If your hyperlinks match your sheet names then the following should work

Your sheet names have to match your hyperlink text for this code to work.

 

[icedancekid]

Afternoon, It would be great if I could get your help?

I am also recieving an Error Message when using the code outlined above. The error I am recieving is as follows:

Run-time error '13':

Type mismatch

I am completely new to MVB so any help would be hugely appreciated.

Many thanks.