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
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 statementto 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.
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
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 statementwill 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.
Read More: How to Create Table of Contents Without VBA in Excel
Download Workbook
[/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.