Index Sheet for Worksheets in Ms Excel

Using an index sheet for worksheets in ms excel will help you to quickly and easily navigate throughout your workbook so that with one click of the mouse, you will be taken exactly where you want to go, without fuss. You can create an index in a couple of ways.
Simply create the index by hand. 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 > Hyperlink… or by pressing Ctrl/-K.
The following code will automatically create a clickable, hyperlinked index of all the sheets you have in the workbook.
This code should live in the private module for the Sheet object. Insert a new worksheet into your workbook and name it something appropriate—Index, for instance. Right-click the index sheet’s tab and select View Code from the context menu. Enter the following Visual Basic code (Tools > Macro > Visual Basic Editor or Alt/Option-F11):

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

Go back to your workbook and then save your changes.
Another, more user-friendly, way of creating an index sheet for your worksheets in ms excel is to add a link to the list of sheets as a context-menu item, keeping it just a right-click away as shown below.

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(“Sheet Index”).Delete

On Error GoTo 0

Set cCont = Application.CommandBars(“Cell”).Controls.Add _

(Type:=msoControlButton, Temporary:=True)

With cCont

.Caption = “Sheet Index”

.OnAction = “IndexCode”

End With

End Sub

Next, insert a standard module to house the IndexCode macro, called by the above 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 as Workbook_SheetBeforeRightClick will mean Excel will not know where to find the macro called IndexCode.

Select Insert > Module and enter the following code:
Sub IndexCode( )

Application.CommandBars(“workbook Tabs”).ShowPopup

End Sub

Press Alt/-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 Sheet Index that will take you right to a list of sheets in the workbook.

Similar Posts

• Simultaneous Data Entry into Multiple Worksheets in Ms Excel
• Lock and Protect Cells Containing Formulas in Ms Excel
• Hide & Unhide Worksheets in MS Excel
• Hide Error Values with Conditional Formatting in Ms Excel
• Identify & Shade Weekends with conditional formatting in Excel