DataWright Information Services

Consulting and Resources for Excel and Access

Navigation add-in for Excel 2007

Download it here:

Notes:

This file is a demonstration of an Excel 2007 Add-In that creates a very basic Navigation tab.

On that tab is a drop-down list that repopulates when you click it, generating a list of worksheets in the active workbook . Selecting an item in the drop-down navigates to the corresponding sheet.

The code is unlocked so that you can go behind the scenes and see how it works. If you want to examine the Ribbon XML you will need to change the file extension from .xlam to .xlsm -- but do that with a copy of the add-in, so the original still works.

To use:

  1. Copy the Add-In to an Add-Ins folder. The Add-In has been tested in these locations --
    • Windows XP:  C:\Documents and Settings\<user name>\Application Data\Microsoft\Add Ins (note: to get to this you will have to set the Documents and Settings folder properties to view hidden files and folders)
    • Vista: C:\Program Files\Microsoft Office\Office 12\ADD-INS
    • Vista: <user name>\AppData\Roaming\Microsoft\AddIns
  2. Load the Add-In --
    • Office button > Excel Options > Add Ins > Manage > Go

You should see a Navigate tab appear on the ribbon. To navigate to another sheet, select the Worksheet Selector dropdown in the Navigation group.

The Worksheet Selector works with any Excel file, once the add-in is loaded. As you switch to a new file the dropdown list is repopulated.

Acknowledgements:

I am standing on the shoulders of giants. This sample borrows heavily on an article by Ken Puls, as well as information from Chapter 14 of the Excel 2007 VBA Programmer's Reference, available as a free download.

Warning:

This add-in has been built for Excel 2007. You cannot install it into earlier Excel versions.

The add-in does no error checking. It has been tested in Excel 2007 on Windows XP SP2 and Windows Vista Business SP1.

You must ensure that none of your worksheet tab names contain an apostrophe (') or ampersand (&). If they do, the worksheet list will be blank.