SheetName = TOCBook.Sheets(SheetCount).Name TOC.Cells(StartRow - 2, 'B').Font.Size = 10įor SheetCount = 1 To TOC.Cells(StartRow - 2, 'B').Value = 'Hidden sheets are italicized' TOC.Cells(StartRow - 3, 'B').Value = 'TABLE OF CONTENTS' Overwrite?', vbYesNo + vbDefaultButton2, 'Overwrite TOC?') vbYes Then Exit Sub If MsgBox('Table of contents already exists. MsgBox 'You must have a workbook open first!', vbInformation, 'No Open Book' 'Set a constant to the name of the Table of Contents not chart sheets), ' overwrite anything in the destination cell (unless worksheet is protected) ' 'Call CreateTOC(True, False) ' This will create a Table of Contents which includes hidden sheets and does not add a link ' back to itself ' 'Declare all variables '*** CAUTION: Specifying a cell in each sheet will 1) only work on worksheets (i.e. not chart sheets), ' overwrite anything in the destination cell (unless worksheet is protected) ' 'Call CreateTOC(False, True) ' This will create a Table of Contents which excludes hidden sheets and also includes a link ' back to itself.

' 'Use cases: 'Call CreateTOC(False, False) ' This will create a Table of Contents which excludes hidden sheets and does not add a link ' back to itself ' 'Call CreateTOC(True, True) ' This will create a Table of Contents which includes hidden sheets and also includes a link ' back to itself. not chart sheets), ' will not work with a protected sheet, and will overwrite anything in the cell ' specified in the destination constant below (under declared variables). This will only be placed on worksheets (i.e.

' ' IncludeHiddenSheets ' Boolean ' Specifies whether or not hidden sheets should be included in the Table of Contents ' ' AddHomeLinkOnSheets ' Boolean ' Specifies whether or not a link should be placed in each sheet linking back to the ' Table of Contents. Optional ByVal AddHomeLinkOnSheets As Boolean = False) Sub CreateTOC(Optional ByVal IncludeHiddenSheets As Boolean = False, _ Note: This procedure was originally intended for use in a Personal.xls (global) file. UPDATED: This will create a hyperlink to Chart sheets as well, with some additional programming. This is especially good on larger workbooks.

If desired, you can create a hyperlink back to the Table of Contents as for a well-rounded navigation system. Each hyperlink will bring you to cell A1 of that sheet. This code will allow you to quickly and easily setup a Table of Contents for a specific workbook and have each sheet name numbered chronologically as hyperlinks to those specific sheets. This code creates/updates a Table of Contents for a specific workbook.Īs often as is the case, workbooks become tiresome to navigate through the sheet tab buttons.

Excel for mac name a table