Kapat

Create Table Of Contents With Macro

excel  listbox context menu Create The Workbook’s Index

The table of contents is a useful feature, especially for workbooks with a lot of sheets. To create the table of contents, we will use a macro. In this way, the table of contents is easily created automatically.

In our template when the macro is triggered, hyperlinks are created in the index sheet for all sheets of the workbook . When clicked this hyperlinks, the user can go to the related sheet. In addition, by pressing Esc key on the keyboard while on any sheet, user can return to the sheet that contained the table of contents.

We will follow these steps to create a table of contents in Excel workbook : excel table of contents
1️⃣ VBA Editor window is opened by pressing Alt + F11 keys on any sheet. Insert -> Module is selected from the context menu on the opened when right-clicking on ThisWorkbook item .

2️⃣ By double clicking on the added Module, the following codes are added to this Module : excel vba table of contents

Sub Create_Workbook_Index()
Dim Page As Worksheet
Dim k, m As Integer
k = 1
m = 1
NewSheet ("Workbook_Index")
For Each Page In Worksheets
Sheets("Workbook_Index").Cells(k, 2).Select
Sheets("Workbook_Index").Cells(k, 1).Value = " " & m & " " & "."

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Page.Name & "!A1", TextToDisplay:=Page.Name
k = k + 1
m = m + 1
Next Page
With Sheets("Workbook_Index")
.Columns(1).Interior.Color = RGB(226, 252, 214)
.Cells.RowHeight = 18
.Columns(1).Cells.HorizontalAlignment = xlHAlignRight
.Columns(2).Cells.HorizontalAlignment = xlHAlignLeft
.Columns(2).Interior.Color = RGB(255, 234, 159)
.Columns(1).EntireColumn.AutoFit
.Columns(2).EntireColumn.AutoFit
End With
Application.OnKey "{ESC}", "Index_page"
End Sub

Function NewSheet(argCreateList)
    For Each Worksheet In ThisWorkbook.Worksheets
        If argCreateList = Worksheet.Name Then
             Application.DisplayAlerts = False
            Worksheet.Delete   'If found - delete it
        End If
    Next Worksheet
    Worksheets.Add(Before:=Worksheets(1)).Name = argCreateList
End Function

Sub Index_page()
Dim sht As Worksheet, answr As String, exst As Boolean

For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "Workbook_Index" Then
        exst = True
    End If
Next

If Not exst Then
answr = MsgBox("Workbook_Index sheet does not exist. Would you like to create this sheet?", vbQuestion + vbYesNo + vbDefaultButton2, "Alert")
If answr = vbYes Then
  Call Create_Workbook_Index
Else
 Exit Sub
End If
Else
Sheets("Workbook_Index").Activate
End If

Call Create_Workbook_Index
End Sub

Sub auto_open()
Call Create_Workbook_Index
End Sub
vba table of contents

3️⃣ Then, by clicking Alt + F8 on any Excel sheet, Macro window is opened. Create_Workbook_Index named macro is selected in the opened Macro window and Run button is clicked.
A sheet named “Workbook_Index” is created by the macro as the first sheet of the workbook. On this sheet (Workbook_Index), the sheets in the workbook are sorted along with their order numbers, and hyperlinks are created for the sheets.
Thus, the tab of the clicked sheet becomes active. By pressing Esc key while on any sheet, user can 🔙 back to the Workbook_Index sheet .
Application.OnKey "{ESC}", "Index_page"

excel assign macro to key Also, when a new sheet is added to the workbook or the sheet is deleted, then pressing Esc key will update the index sheet.

excel create table of contents macro

Or, it is enough to close and reopen the workbook after adding VBA codes to the module without applying the operations in the third step. When the workbook is opened, the Create_Workbook_Index procedure will start automatically with the following codes : excel vba auto open
Sub auto_open()
Call Create_Workbook_Index
End Sub

📥 Download sample workbook