VBA - Index of Contents Sheet with links back to Index Contents Sheet


I was wondering if someone has a VBA code that will create a worksheet that inserts a table of contents that links to other worksheets within the same workbook.

However I also require the VBA code to be able to have links with the other worksheets so you click and return to the table of contents worksheet.

I have seen lots of VBA code on the internet just for the table of contents but not where you have links going back as well.

As you will appreciate it not good just have a link one way to other worksheets for the user - they require links to go back to the table of contents.

Appreciate it if you can assist.



Try this code in sheet module of the Index sheet.

Also includes a goto index sheet link on every sheet.

Index changes as sheets are added or removed from workbook.

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long

l = 1

    With Me
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
    End With
    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name Then
            l = l + 1
                With wSheet
                    .Range("A1").Name = "Start_" & wSheet.Index
                    .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
                    SubAddress:="Index", TextToDisplay:="Back to Index"
                End With

                Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
                SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
        End If
    Next wSheet

End Sub


2 people found this reply helpful


Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.


Question Info

Last updated November 27, 2020 Views 228 Applies to: