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 SettingsApplication
      DataMicrosoftAdd 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
      FilesMicrosoft OfficeOffice 12ADD-INS
    • Vista: AppDataRoamingMicrosoftAddIns
  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.