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.

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.

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

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

• 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.

Read More: How to Create Table of Contents Automatically in Excel

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

• Press F5.

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

Read More: How to Create Table of Contents Without VBA in Excel

Download 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.

Related Articles

• How to Create Table of Contents in Excel with Hyperlinks
  
• How to Create Table of Contents in Excel with Page Numbers
• How to Create Table of Contents for Tabs in Excel