TOC creator in Excel Workbook using VBA

Home/VBA Code Explorer/Projects/TOC creator in Excel Workbook using VBA

TOC in Excel Workbook using VBA :Project Objective

TOC (Table of Contents) in Excel Workbook using VBA. Createing hyperlink to all worksheet names in the TOC worksheet. Create back link in all worksheet to TOC worksheet, So that easy to navigate to all worksheets in the workbook. Following is the step by step detailed explanation to automate this project using VBA. How we are creating TOC? Let us see!

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


TOC in Excel Workbook using VBA

TOC 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 explantion 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

LIMITED TIME OFFER

Leave A Comment