Excel

Create an Index Page or Table of Contents

Ease of Use

Intermediate

Version tested with

 

Submitted by:

Steiner

Description:

Creates hyperlinks to all other worksheets and also creates links back to the index (or home) page. 

Discussion:

This is like an instant menu for your workbook. Some people call it a "dashboard" or "home" page. This procedure creates links to all other worksheets in the same workbook on a new sheet called Index. Then, it inserts on each worksheet a link back to the Index sheet. The links are inserted right where the cursor is when the macro is run. 

Code:

instructions for use

			

Option Explicit Sub IndexIt() Dim Ws As Worksheet, WsInd As Worksheet, lStartRow%, lStartCol, sBackRange As String '##1: Where should the back-to-index-page link be, change if necessary sBackRange = "A1" lStartRow = Selection.Row lStartCol = Selection.Column Set WsInd = ActiveSheet 'Add the links For Each Ws In Worksheets If Ws.Name <> WsInd.Name Then WsInd.Hyperlinks.Add WsInd.Cells(lStartRow, lStartCol), "", "'" & Ws.Name & "'!A1" WsInd.Cells(lStartRow, lStartCol).Value = Ws.Name lStartRow = lStartRow + 1 '##2: Add link back to index, comment the following 2 lines if you don't want this part Ws.Hyperlinks.Add Ws.Range(sBackRange), "", "'" & WsInd.Name & "'" & "!A1" Ws.Range(sBackRange).Value = "Back to Index" End If Next Ws WsInd.Activate End Sub

How to use:

  1. Copy the code above.
  2. Open your workbook and hit Alt+F11 to open the VB Editor (VBE).
  3. From the menu, choose Insert-Module.
  4. Paste the code into the code area of the module at right.
  5. If you want to have the "back-links" somewhere other than Cell A1 on each worksheet, change the line with the comment ##1 giving the address you like as a String like "C3"
  6. If you don't want to have links back to the index page at all, uncomment the 2 lines right after ##2 by placing an apostrophe in front of them.
  7. Close the VBE and save your file.
  8. Once you have run the code in your workbook, you MAY have no more need for it and you may want to delete it.
 

Test the code:

  1. Create a worksheet called Index and place your cursor in the cell where you want the index links to begin.
  2. Go to Tools-Macro-Macros and double-click IndexIt.
 

Sample File:

Hyperlinks.zip 8.28KB 

Approved by mdmackillop


This entry has been viewed 322 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express