Ask a new question

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

Hi

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.

Zia  

Answer
Answer

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
        .Columns(1).ClearContents
        .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

Gord

3 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 January 13, 2022 Views 1,139 Applies to: