Linking worksheets within a workbook

Microsoft Excel 2002 (full product)
April 16, 2010 at 11:50:41
Specs: Windows XP
Hello, I have a workbook that has about 30 different worksheets in it. Is it possible to create a link at the beginning of the workbook so that I can jump to a specific worksheet?

See More: Linking worksheets within a workbook

Report •

April 16, 2010 at 12:40:01
How about this...

When you run it the first time it will:

1 - Create a TOC sheet as the new first sheet
2 - Fill Column A with the name of each sheet
3 - Link each sheet name to cell A1 of that sheet.

If you add, delete or change sheet names, run it again and it will clear the TOC and re-populate the TOC with new links.

I suggest you try this in a back-up copy of your workbook in case things go horribly wrong. Since data is being deleted and added, you don't want to destroy your original workbook if I've made an error.

Option Explicit
Sub CreateTOC()
Dim nxtSht As Integer
'Check for existing TOC
If Sheets(1).Name = "TOC" Then
'If it exists, delete all link to allow for updates
'If not, add one
  ActiveWorkbook.Sheets.Add Before:=Worksheets(1)
  ActiveSheet.Name = "TOC"
End If
'Put text in A1 of TOC
  Sheets("TOC").Range("A1") = "Sheet Name"
'Loop through sheets, creating hyperlinks in A2, A3, A4, etc.
'that link to Sheet2, Sheet3, sheet4, etc.
'Put corresponding Sheet Name in cell
  For nxtSht = 2 To Sheets.Count
   Sheets("TOC").Hyperlinks.Add Anchor:=Sheets("TOC").Range("A" & nxtSht), Address:="", SubAddress:= _
        Sheets(nxtSht).Name & "!A1", TextToDisplay:=Sheets(nxtSht).Name
End Sub

Report •

April 17, 2010 at 07:40:48

An alternative approach is to have a drop-down menu on the toolbar.

This approach creates the drop-down menu whenever the workbook is opened, so it is 'auto-updating', adding or removing worksheet names from the drop-down list as the workbook changes.

This menu is removed from the toolbar when the workbook is closed.

Also the drop-down list of worksheet names is available to users wherever they are in the workbook - they can use the list from any worksheet.

It will take longer to setup than DerbyDad03's solution as there is code in two places.

1. Add the main menu part of the code - this consists of code that will create the menu when the workbook is opened and code that will remove the menu item when the workbook is closed:
Click Alt + f11 (the Alt key and function key 11 together)
This opens the Visual Basic window
On the left is the Project Explorer pane. If it is not visible, click 'View' from the VB menu and select 'Project Explorer'
Find your workbook - it will look something like this:
VBAProject (YourWorkbookName.xls)
Underneath are various Excel objects - double click 'ThisWorkbook' (It comes after the list of the worksheets in the workbook) - note that you must double click 'ThisWorkbook' to get to the right place.
In the VB window on the right enter this:

Option Explicit

Private Sub Workbook_Open()
'This subroutine will load a toolbar menu item
'to jump to any worksheet in this workbook

On Error Resume Next

Dim w As Worksheet
Dim cb1 As CommandBar
Dim btn As Object
Dim NewMenuWBS As CommandBarControl
Dim NewSubMenu As CommandBarButton

On Error GoTo ErrHnd

'Clear any copies of the Worksheets menu
Set cb1 = Application.CommandBars("Worksheet Menu Bar")
For Each btn In cb1.Controls
    If btn.Caption = "Worksheets..." Then
    End If
'Add new Worksheets menu
 Set NewMenuWBS = Application.CommandBars("Worksheet Menu Bar"). _
 With NewMenuWBS
     .Caption = "Worksheets..."
 End With

'add sub menu items
For Each w In ActiveWorkbook.Worksheets
    Set NewSubMenu = NewMenuWBS.Controls.Add(Type:=msoControlButton)
    With NewSubMenu
        .Caption = w.Name
        .OnAction = "'WorksheetJump " & Chr(34) & w.Name & Chr(34) & "'"
    End With
Next w
Exit Sub

'error handler
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Clear all copies of the Worksheets menu

Dim cb1 As CommandBar
Dim btn As Object

Set cb1 = Application.CommandBars("Worksheet Menu Bar")

For Each btn In cb1.Controls
    If btn.Caption = "Worksheets..." Then
    End If
End Sub

2. Add the macro that carries out the jumps:
Go back to your workbook in the Project Explorer pane.
Right-click on the VBAProject(YourWorkbookName.xls) and select 'Insert' and then 'Module' (not Class module)
New items will appear in the project explorer - 'Modules' and underneath - 'Module1' (click the + button if Module1 is not shown)
Double-click Module1.
In the Properties pane change the name to 'WSMenu'
If the Properties pane is not visible below the Project explorer pane, select 'View' from the VB menu and select 'Properties Window'
In the main VB window enter this code:
Sub WorksheetJump(wsName As String)
End Sub

From the VB Menu click File - Save(YourWorkbookName.xls)
Use Alt+f11 to return to the main Excel window.
Save and close the workbook.
Now reopen it and you should see a new menu item on the standard toolbar - 'Worksheets...'
Click the name and you will get a drop-down list of all the worksheets in the workbook.
Click any one to jump to that sheet.

Although I have used this menu approach before, I had not made it dynamic, i.e., the worksheet names in the drop-down are created each time the workbook is opened and therefore the list may change.
For the dynamic menu to work, the drop-down list has to pass the selected worksheet name to the WorksheetJump macro as an argument. I had difficulty getting this to work and the final solution was based on this post: here - see the post by tusharm.
tusharm credits Tom Ogilvy with the solution to passing arguments from the button/menu item OnAction function and the arrangement of double and single quotes.
This was the final code I used - Chr(34) is a double quote:

.OnAction = "'WorksheetJump " & Chr(34) & w.Name & Chr(34) & "'"


Report •

April 26, 2010 at 11:16:03
Thank you to both DerbyDad and Humar. I was able to get it working. Now I get the fun task of making it look nice for the boss.

Report •

Related Solutions

October 6, 2010 at 11:01:28
Thanks so much for this useful tool.

I was able to get it to work but I have a small problem. Whenever the worksheet name has a space in it (for example, a sheet named "John Smith") the program doesn't work and it gives an error box saying "Reference is not valid". On the other hand the program is working fine whenever the worksheet name has no spaces (e.g. a sheet named "Retailers").
Is there a reason why that is happening?

Report •

Ask Question