Link to the Index from a Context Menu

The third 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. You generally get to this command bar by right-clicking any of the sheet tab scroll arrows on the bottom left of any worksheet.

To link that tab's command bar to a right-click in any cell, enter the following code in the private module of ThisWorkbook:

    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, you'll need to insert a standard module to house the IndexCode 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 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, then save your workbook. 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.