Create Index For WorkBook

Create The Workbook's Index With VBA Codes


        In this template,  hyperlinks are created in the index sheet for all  sheets of the workbook . When clicked this hyperlinks, can be went to the related address.

When "ESC key" is pressed, can be returned to the index sheet. Index page is automatically updated when a new sheet is added.

↳ To create a table of contents in any excel workbook, these steps must be followed.To create a table of contents in any excel workbook, these steps must be followed:

𝟭. Insert -> Module is selected from the context menu on the opened when right-clicking on Workbook item  in the "Project-VBAProject" panel.

𝟮. The following codes are entered into the module:
Sub Create_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(215, 250, 198)
.Cells.RowHeight = 18
.Columns(1).Cells.HorizontalAlignment = xlHAlignRight
.Columns(2).Cells.HorizontalAlignment = xlHAlignLeft
.Columns(2).Interior.Color = RGB(255, 255, 163)
.Columns(1).EntireColumn.AutoFit
.Columns(2).EntireColumn.AutoFit
End With
Application.OnKey "{ESC}", "Index_page"     'When pressed the Esc key, it runs the Index_page method 
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()
    Sheets("Workbook_Index").Activate
End Sub

𝟯. Then, by clicking + on any excel sheet, the Macro window is opened. Create_Index macro is selected in the opened Macro window and Run button is clicked.
The macro will automatically create the workbook index (table of contents) on the Workbook_Index sheet that created by the macro.

excel workbook index


No comments:

Post a Comment