- Manually Creating Worksheets Index
- Creating Worksheets Index with HYPERLINK Formula
- Using VBA to Create Sheet Index
- Showing Sheets Index in Right-Mouse Click Context Menu
Manually Creating Sheet Index
You might be tempted to create the index manually:
- Create a new worksheet, call it
Index
or the like, - Enter a list of all your worksheet’s names, and
- Hyperlink each to the appropriate sheet by selecting
Insert » Link
or by pressingCtrl+K
(or⌘+K
). - In the dialog box, click
Place in This Document
and then select the sheet you want to link.
Although this method is probably sufficient for limited instances in which you don’t have too many sheets and they won’t change often, you’ll be stuck maintaining your index by hand.
Using Excel Formula to Create Worksheet Index
You can also use the HYPERLINK
formula to create an index of worksheets manually in Excel:
=HYPERLINK("#Sheet1!A1", "Go to Sheet1")
- In cell
A1
type the name of the first worksheet in your workbook. - Drag the fill handle down to populate the rest of column
A
with worksheet names. - In cell
B1
, type this formula:=HYPERLINK("#'"&A1&"'!A1","Go to "&A1)
- Drag the fill handle down to populate the rest of column
B
with worksheet links.
Using VBA to Automatically Create Index of All the Sheets
The following code will automatically create a clickable, hyperlinked index of all the sheets you have in the workbook. The index is re-created each time the sheet that houses the code is activated. This code should live in the private module for the Sheet
object.
- Insert a new worksheet into your workbook and name it Index.
- Right-click the index sheet’s tab and select
View Code
from the context menu. Or clickAlt+F11
( for MacOpt+F11
orFn+Opt+F11
). - Enter the following Visual Basic code in the
Sheet1 (Index)
object:
Private Sub Worksheet_Activate() Dim wSheet As Worksheet Dim l As Long l = 1 With Me .Columns(1).ClearContents .Cells(1, 1) = "INDEX" .Cells(1, 1).Name = "Index" End With For Each wSheet In Worksheets If wSheet.Name <> Me.Name Then l = l + 1 With wSheet .Range("A1").Name = "Start" & wSheet.Index .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:= _ "Index", TextToDisplay:="Back to Index" End With Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _ SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name End If Next wSheet End Sub
Press Alt+Q
(or ⌘+Q
) to get back to your workbook and then save your changes. Notice that the code names
(such as when you name
a cell or range of cells in Excel) cell A1
on each sheet Start
, plus a unique whole number representing the index number of the sheet. This ensures that A1
on each sheet has a different name. If A1
on your worksheet already has a name, you should consider changing any mention of A1
in the code to something more suitable-an unused cell anywhere on the sheet, for instance.
Create Worksheet Index When Right-Click on a Cell
Another, more user-friendly, way of constructing an index is to add a link to the list of sheets as a context-menu item, keeping it just a right-click away. We’ll have that link open the standard workbook tabs command bar.
- To link that tab’s command bar to a right-click in any cell, enter the following code in the VBE:
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Dim cCont As CommandBarButton On Error Resume Next Application.CommandBars("Cell").Controls("Show Sheets").Delete On Error GoTo 0 Set cCont = Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, Temporary:=True) With cCont .Caption = "Show Sheets" .OnAction = "sheetsPopup" End With End Sub
- Next, you’ll need to insert a standard module to house the
sheetsPopup
macro, called by the preceding code whenever the user right-clicks in a cell. It is vital that you use a standard module next, as placing the code in the same module asWorkbook_SheetBeforeRightClick
will mean Excel will not know where to find the macro calledsheetsPopup
.
In VBA window, selectInsert » Module
and enter the following code:
Sub sheetsPopup( ) Application.CommandBars("workbook Tabs").ShowPopup End Sub
- Press
Alt+Q
or⌘+Q
to get back to the Excel interface.
- Now, right-click within any cell on any worksheet and you should see a new menu item called
Popup Sheets
that will take you right to a list of sheets in the workbook.