TOC creator in Excel Workbook using VBA

How we are going to develop this TOC creator(The KEY steps) :

To create Table of contents(TOC) in Excel Workbook, minimam one or more sheet(s) should be exist.
Let me explain the key steps to develop this TOC creator project. We are going to write a procedure (Create_TOC_InWorkbook) with the below approach.

• Step 1: Variable Declaration: We will be declaring required variables and objects which are using in our procedure.
• Step 2: TurnOff screen update and Events: We are temporarily avoiding screen flickering and events triggering in the application.
• Step 3: Delete old TOC Worksheet: Before creating new TOC Worksheet, we have to check if there is any existing Worksheet with the same name and delete it.
• Step 4: Add new TOC Worksheet: Lets add new worksheet, named it as “TOC”.
• Step 5: Loop through each Worksheet: Now, let us loop through all worksheets and create TOC.
• Step 5.1: Create hyperlink in TOC Worksheet: Create hyperlink to all worksheet names in the TOC worksheet.
• Step 5.2: Create Back Link to all Worksheets: Create Back link in all worksheet to TOC worksheet. So that easy to navigate to all worksheets in the workbook.
• Step 6: Formating the 2nd Column in TOC Sheet: Formating (like Alignment, Size of the header text, Column Width, etcc) the 2nd column in TOC sheet.
• Step 7: TurnOn screen update and Events: Let’s reset the screen update and events of application.

Code and explanation for each control:

Step 1: Declaring variables which are using in the entire project.

Dim iCnt As Integer
Dim Sht As Worksheet, TocSht As Worksheet
Dim ShtName As String

Step 2: Disable Screen Updating is used to stop screen flickering and Disable Events is used to avoid interrupted dialog boxes or popups.

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With 

Step 3: Deleting the ‘TOC’ Worksheet if it exists in the Workbook. And Display Alerts is used to stop popups while deleting Worksheet.

Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Sheets("TOC").Delete
Application.DisplayAlerts = True

Step 4: Adding a new WorkSheet at the end of the Worksheet. Naming as ‘TOC’. And finally it is assigned it to object (TocSht).

With ActiveWorkbook
    Set TocSht = .Sheets.Add(After:=.Sheets(.Sheets.Count))
    TocSht.Name = "TOC"
End With 

Step 5: It is looping through each (or all) WorkSheet(s) in the workbook.

For Each Sht In ThisWorkbook.Worksheets
    If Sht.Name <> "TOC" Then
    End If
Next

Step 5.1: Create hyperlinks for all Worksheets in TOC Worksheet

With Sheets("TOC")
            .Activate
            .Range("B" & iCnt).Select
            .Hyperlinks.Add Anchor:=Selection, Address:="", _
             SubAddress:=ShtName & "!A1", TextToDisplay:=iCnt - 1 & ". " & ShtName
        End With

Step 5.2: Create Back Links to all the Worksheets in the Workbook.

With Sheets(ShtName)
            .Activate
            .Hyperlinks.Add Anchor:=Range("A1"), Address:="", _
                SubAddress:="TOC!A1", TextToDisplay:="Back to TOC"
            .Range("A1").EntireColumn.AutoFit
        End With

Step 6: Formating (like Alignment, Size of the header text, Column Width, etcc) the 2nd column in TOC Worksheet.

With Sheets("TOC")
    .Activate
    .Range("B1") = "Table of Contents"
    .Range("B1").Font.Size = 16
    .Range("B1").EntireColumn.AutoFit
    .Columns("B:B").HorizontalAlignment = xlLeft
End With

Step 7: Enableing or TurnOn Screen Update and Events at the end of the project.

With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

Final VBA Module Code(Macro):

Please find the following procedures to create TOC project.

Sub Create_TOC_InWorkbook()
On Error GoTo ErrOccered

'1. Variable Declaration
Dim iCnt As Integer
Dim Sht As Worksheet, TocSht As Worksheet
Dim ShtName As String
iCnt = 2

'2. TurnOff Screen updating and Events
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

'3. Delete the 'TOC' WorkSheet if it exists
Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Sheets("TOC").Delete
Application.DisplayAlerts = True

'4. Add a new WorkSheet and name as 'TOC'
With ActiveWorkbook
    Set TocSht = .Sheets.Add(After:=.Sheets(.Sheets.Count))
    TocSht.Name = "TOC"
End With

'5. Loop through each WorkSheet in the workbook to create TOC
For Each Sht In ThisWorkbook.Worksheets
    If Sht.Name <> "TOC" Then
        ShtName = Sht.Name  'Worksheet Name
        
        '5.1 Create hyperlink in TOC Worksheet
        With Sheets("TOC")
            .Activate
            .Range("B" & iCnt).Select
            .Hyperlinks.Add Anchor:=Selection, Address:="", _
             SubAddress:=ShtName & "!A1", TextToDisplay:=iCnt - 1 & ". " & ShtName
        End With
        
        '5.2 Create Back Link to all the Worksheets in the Workbook
        With Sheets(ShtName)
            .Activate
            .Hyperlinks.Add Anchor:=Range("A1"), Address:="", _
                SubAddress:="TOC!A1", TextToDisplay:="Back to TOC"
            .Range("A1").EntireColumn.AutoFit
        End With
        
        'Increment iCnt value for next row in TOC Worksheet
        iCnt = iCnt + 1
    End If
Next

'6. Formating the 2nd Column in TOC Sheet
With Sheets("TOC")
    .Activate
    .Range("B1") = "Table of Contents"
    .Range("B1").Font.Size = 16
    .Range("B1").EntireColumn.AutoFit
    .Columns("B:B").HorizontalAlignment = xlLeft
End With

ErrOccered:
'7. TurnOn Screen updating and Events
With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub

Assign TOC macro to a Shape on the Worksheet:

Here are steps to create TOC in the workbook.

1. Place any shape by clicking on insert menu from illustrations group.
2. Right click on the shape, selct assign macro.
3. select the macro name(‘Create_TOC_InWorkbook’) from the available list and click on OK button.
4. Now, go to the Developer tab.
5. Design Mode should be turned off from the Controls group.
6. Now, go back to the shape and click on the created shape to see the TOC Worksheet in the Workbook.

Instructions to Execute the Procedure:

You can download the below file and see the code and execute it. Or else, you create new workbook and use the above code and test it. Here are the instructions to use above code.

1. Open VBA Editor window or Press Alt+F11.
2. Insert a new module from the Insert menu.
3. Copy the above procedure and paste it in the newly created module.
4. You can hit F5 key from the keyboard and you can see the TOC Worksheet at the end of all Worksheets in the workbook.

Download the TOC creator – Excel VBA Project:

Here is the TOC project workbook macro file to explore yourself.

Create TOC in Workbook using Excel VBA