Creating a Table Of Contents with VBA


Creating a table of contents page is a handy tool when building a recurring report in Excel.  It gives users useful information about the nature of the file and if designed well, can provide a navigation tool for the Excel file (the model).  One of the issues with any TOC is that as the model evolves, the TOC needs to evolve with it.  MS Word has a very handy tool for creating a TOC which moves and updates with the Word document.  MS Excel has not produced something as robust just yet but I have my fingers crossed for future iterations. 
 
By combining the Table of Contents with a Worksheet Activate Event the TOC sheet can stay in line with the movements of a report as sheets are added and deleted.  Additionally if you use a hyperlink function the Index page will act as a navigation tool to the sheets within the workbook.  With the combination of these two functions the Index page can move and adapt in line with the Excel model.
 
One of the issues in creating a TOC page is the Chart Sheet.    A Chart Sheet is a sheet dedicated to a particular chart.  Chart Sheets and worksheets are two different types of collections, which creates issues as they cannot be treated the same. In the following procedure I have trapped the instance of a chart sheet.  The TOC will skip over this sheet if it exists and place the chart sheet to the end of the TOC.  So the chart sheets are included in the TOC but not necessarily in the order they appear in the Report.  I have found using Chart sheets a little restrictive so tend not to use them.  Suffice to say the procedure works best in workbooks containing worksheets with charts contained within the sheets, for arguments sake we will call this a normal workbook.  In these circumstances the following procedure works really well.
 

Table of Contents Excel

This is the Table of Contents Page.  The file will create a Hyperlink to Each Sheet.  To Test the procedure add a Sheet and Click on the TOC page.  The Sheet will be added to the list.  The same is true if you delete a sheet.

The code to run the procedure is as follows;

Option Explicit
Private Sub Worksheet_Activate()
'The following clears then rebuilds the Index page. Processes hidden sheets.
'Assumes the Chart sheets to end, just in case a user has created one.
Dim sh As Worksheet
Dim ws As Worksheet
Dim cs As Chart 'Chart Sheet
Dim i As Integer

Application.Calculation=xlManual
Application.ScreenUpdating=0
Set sh=Sheet1 'Index sheet, where list will live.
i=10 'Line the Code starts on
sh.Range("G10:H200").ClearContents 'Clear original list

For Each Ws In ActiveWorkbook.Worksheets 'Active code to create fresh Index
If ws.Name <> sh.Name Then
sh.Range("G" & i).Value="Page " & i - 9
sh.Hyperlinks.Add sh.Range("H" & i), "", "'" & ws.Name & "'!A1", , ws.Name
i=i + 1
End If
Next ws
'Error trap for Chart Sheets.
For Each cs In ActiveWorkbook.Charts
sh.Range("G" & i).Value="Chart " & i - 9 'Code starts on Line 10 Less 9=1
sh.Range("H" & i).Value=cs.Name 'No Hyperlink
i=i + 1
Next
Range("G10", Range("H" & Rows.Count).End(xlUp)).Font.Size=12 'resize font
Application.Calculation=xlAutomatic
End Sub

Here is an example file which creates the table of contents page.  To test it add a sheet to the workbook and name it.  Now click on the Index (TOC) page and you will notice your newly created sheet is apart of the table of contents.  I fear this procedure may become redundant in later versions of Excel as Microsoft incorporate a TOC feature into Excel in the same way that Word has this feature.  But for now the above is a very nice tool.