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

Get FREE Advanced Excel Exercises with Solutions!

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.

table of contents in excel vba

table of contents in excel vba

table of contents in excel vba

table of contents in excel vba

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

table of contents in excel vba

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

  • Go to the Insert tab >> Module

first sheet

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

first sheet

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.

first sheet

  • Press F5.

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

first sheet

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.

first sheet

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

first sheet

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

first sheet

  • 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

first sheet

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

first sheet

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

first sheet

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

first sheet

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.

table of contents in excel vba

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

any sheet

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

any sheet

  • Press F5.

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

any sheet

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


Download Workbook

Table of Contents.xlsm
[/wpsm_box

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

<< Go Back To Table of Contents in Excel | Hyperlink in Excel | Linking in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo