How to Make Table of Contents Using VBA in Excel (2 Examples)

If you are looking for ways to make a table of contents in Excel VBA quickly, then this article is for you. So, let’s dive into the main article to know more about the ways of doing this job automatically.

Table of Contents hide

Download Workbook

What Is a Table of Contents?

2 Examples to Make Table of Contents Using VBA in Excel

Example-1: Make a Table of Contents Automatically by Using VBA

Example-2: Make a Table of Contents on Any Sheet in Excel

Conclusion

Download Workbook

Table of Contents.xlsm

What Is a Table of Contents?

A table of contents is generally a list where all of the contents of a book are summarized serially. In a workbook of Excel, 255 sheets can be used up for storing various types of data. But it would be great if we have the list of the name of the sheets and their links to go to those sheets in a shortcut way. A table of contents can make this possible by creating a summarized list of all of the sheets of that book and then creating hyperlinks to go to those sheets quickly.

2 Examples to Make Table of Contents Using VBA in Excel

Here, we have a total of 4 sheets; Physics, Chemistry, Maths, and Biology in a workbook and each of them contains a record of marks for their corresponding subjects. Our task will be to create a summary table where we can make a list of these 4 sheets with their corresponding links so that we can explore these sheets easily within a few moments.

We have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.

Example-1: Make a Table of Contents Automatically by Using VBA

In this section, we will create a sheet for the table of contents which will place itself in the first place prior to the other sheets automatically. Here, we don’t have to create any sheet for this table of contents before running the code. And if we have any sheet containing this table of contents beforehand, then the VBA code will delete this sheet automatically.

Step-01:

Go to the Developer tab >> Code group >> Visual Basic

After that, you will get the Visual Basic for Applications window.

Go to the Insert tab >> Module

In this way, you will create a new module Module 1.

Step-02:

Use the following code in your created module.

Sub making_list_of_subjects()
Dim subject As Integer

On Error Resume Next
Application.DisplayAlerts = False
Sheets("makinglist").Delete
ActiveWorkbook.Sheets.Add Before:=ActiveWorkbook.Worksheets(1)
ActiveSheet.Name = "makinglist"

For subject = 2 To Sheets.Count
ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Cells(subject + 2, 3), _
Address:="", SubAddress:="'" & Sheets(subject).Name & "'!B3", _
TextToDisplay:=Sheets(subject).Name, _
ScreenTip:="contains the record of marks for " & Sheets(subject).Name
With ActiveSheet
.Range("B1") = "Table of Contents"
.Range("B1").Font.Bold = True
.Range("B1").Font.Size = 18
.Range("B3").Value = "Subject"
.Range("B3").Font.Bold = True
.Range("B3").Font.Size = 14
.Range("C3").Value = "Link"
.Range("C3").Font.Bold = True
.Range("C3").Font.Size = 14
.Cells(subject + 2, 2).Value = Sheets(subject).Name
.Range("B4", Range("C" & Rows.Count).End(xlUp)).Font.Size = 12
End With
Next subject

End Sub

Code Breakdown:

We have named the sub-procedure as making_list_of_subjects and declared the subject as an Integer.
On Error Resume Next will ensure if the code is facing an error at any position then it can move to the next line.
DisplayAlerts = False is used to prevent any message box warning that this code is deleting a sheet.
We will be giving the name of the new sheet makinglist and so if there is any previously created sheet with this name then we will delete this sheet by using Sheets("makinglist"). Delete
Then we will be adding a new sheet before all of the sheets using the method Add and the name of this sheet will be the makinglist.
The FOR loop has been used to iterate through sheet 2 to the sheet up to the end and so we have given the limit of the variable subject from 2 to Count
To link the sheet we are using Add method, where Anchor:=ActiveSheet.Cells(subject + 2, 3) is the position of the cell where we want to create the hyperlink (as we want to have the values starting for cell C4), Address:="" is the URL of the hyperlink, SubAddress:="'" & Sheets(subject). Name & "'!B3" represents the position of the cell where we want to go after clicking on the link (we want to go to cell B3 of the respective sheet), TextToDisplay:=Sheets(subject). Name is the text which will appear in the cell, and finally ScreenTip: will show up the text when we put our cursor upon the link.
After that, we used the WITH statement to avoid the need for repetition of mentioning the ActiveSheet object and within this object, we have declared different ranges for entering various texts and we have also made them bold and changed their font sizes.

Press F5.

In this way, we have created a new sheet-makinglist at the first position with the table of contents of this workbook.

Step-03:

You can also do some formatting to make this list more attractive.

Firstly, we increased the column width of Column B and Column C.

You can merge cells B1 and C1 to accommodate the header Table of Contents properly.

Select the cells B1 and C1, go to the Home tab >> Alignment group >> Merge & Center dropdown >> Merge & Center

In this way, our header has been merged and now we can change the alignment of the texts in this table of contents.

Select the contents of the cells and then go to the Home tab >> Alignment group >> click on the Middle Align and Center

After changing alignment, if you want to give a border to the contents then select them and go to the Home tab >> Font group >> Borders dropdown >> All Borders

To change the color of the column headings select the headings and then go to the Home tab >> Font group >> Fill Color dropdown >> Orange, Accent 2, Lighter 80% color (or whatever you want).

Now, if you want to hide the gridlines, then uncheck the Gridlines option under the View

After completing all of the formattings, you will have the following final look at the table of contents.

Example-2: Make a Table of Contents on Any Sheet in Excel

Here, we will create a table of contents in our previously customized sheet makinglist1 and this sheet is not in the first place here. In this sheet, we have done all of the formattings. And now using the VBA code we will make a list of subjects and their corresponding links.

Follow Step-01 of Method-1 to open up the VBA window and insert a module Module2. Keep note of the real name of the sheet for makinglist1 which is Sheet7.

Now, type the following code.

Option Explicit
Private Sub makinglistofsubjects1()
Dim list_sheet, subject_sheet As Worksheet
Dim subject As Integer
Application.Calculation = xlManual
Set list_sheet = Sheet7
subject = 4
list_sheet.Range("B4:C100").ClearContents

For Each subject_sheet In ActiveWorkbook.Worksheets
If subject_sheet.Name <> list_sheet.Name Then
list_sheet.Range("B" & subject).Value = subject_sheet.Name
list_sheet.Hyperlinks.Add Anchor:=list_sheet.Range("C" & subject), Address:="", _
SubAddress:="'" & subject_sheet.Name & "'!B3", TextToDisplay:=subject_sheet.Name
subject = subject + 1
End If
Next subject_sheet
Range("B4", Range("C" & Rows.Count).End(xlUp)).Font.Size = 12

End Sub

Code Breakdown:

We have used Option Explicit for declaring each variable and named the private sub-procedure as makinglistofsubjects1. Due to using private, we can use this code only for this module.
Then we declared the variables list_sheet, and subject_sheet as Worksheet, subject as Integer
Calculation = xlManual will ensure that the calculation procedure will be manual.
Then we set the variable list_sheet to Sheet7 and subject to 4 (the starting row number)
Range("B4:C100").ClearContents is for clearing any type of contents in the sheet.
The FOR loop has been used to iterate through each sheet and the IF statement will check if the sheet name of the sheet on which we are creating the list is not equal to other sheets and if it is true then the rest of the operations will proceed.
To link the sheet we are using Add method, where Anchor:=list_sheet.Range("C" & subject) is the position of the cell where we want to create the hyperlink (as we want to have the values starting for cell C4), Address:="" is the URL of the hyperlink, SubAddress:="'" & subject_sheet. Name & "'!B3" represents the position of the cell where we want to go after clicking on the link (we want to go to cell B3 of the respective sheet), TextToDisplay:=Sheets(subject). Name is the text which will appear in the cell.
Finally, Range("B4", Range("C" & Rows.Count).End(xlUp)).Font.Size = 12 will make the fonts of the texts in the range of the contents to size 12.

Press F5.

Eventually, we created a new sheet-makinglist1 at the second position with the table of contents of this workbook.

Conclusion

In this article, we tried to make a table of contents using VBA in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.