Sheet Switch AddIn for Excel 2010 – Switch between a lot of Sheets

Office 2010 and Office 2013 Only – Please note that the solution here works with Office 2010 and Office 2013

Last year I worked in a specification team for a few month and did a lot in Office Word and Excel. We had one Excel File that contained about 100 Sheets. Switching between the sheets was a very time consuming task. Excel does not offer a comfortable way to navigate, when not all Sheets can be displayed in the navigation bar on the bottom of the Excel window.

Let’s come to look closer, what I mean

Only 5 Sheets are visibile all others are hidden. You must scroll to reach them.

Right-Click to the Sheet-Scrollbar shows the following screen

Using this feature you can switch between the first 15 sheets. This way is acceptable, if you don’t have more that 15 Sheets in your Workbook. Let’s take a more closer look to the “More Sheets…” Option here.

You can see only 12 Sheets by default. To reach more you need to scroll down. The dialog is not realizable. This feature totally useless.

As you can see Excel does not provide a way to switch comfortable between more that 15 Sheets in a Workbook. So I decided to create my own solution.

Excel Sheet Switch

The Add In displays a multi-line switch bar on the bottom of the Excel application’s window, if the Workbook contains more that 5 Sheets.

Download Setup and Source

Office 2010 and Office 2013 Only – Please note that the solution here works with Office 2010 and Office 2013

http://dl.dropbox.com/u/40751518/CodeSamples/Be.ExcelSheetSwitch-1.0.0.4.zip

Download the zip file and extract all files to your disc.

Install the AddIn using Setup

You can use the setup.exe included in the download. Please ignore the security warning and click install – I’m using a Test Certificate there.

Technical Solution

I used Visual Studio 2010 to create a VSTO solution. It tooks me 15 minutes to write the code needed. It’s very simple, so I don’t want to go into details. If you want to know more, you can discover the code in the download above.

Questions

Is there a way to turn the AddIn off?

Yes, the only way is by disabling the COM AddIn in Excel

– Start Excel and click into File / Options / Add-Ins (Tab)

– Manage: COM Add-Ins and click Go…

– Uncheck the Be.ExcelSheetSwitch Add-In

You can enable the AddIn in the same way.

Are there any configuration settings for the AddIn?

ShowSheetCount (Be.ExcelSheetSwitch.dll.config). The default value is 5. The Sheet Switch AddIn will be shown if the workbook contains at least 5 sheets or more. You can change this setting by modifying the configuration file manually for example by using Notepad.

Where can I find the configuration file Be.ExcelSheetSwitch.dll.config?

– Open Windows Explorer and try to find the configuration file with the name

Start with your search at the following directory.

C:\Users\<UserName>\AppData\Local\Apps\2.0\

The complete folder name should look like this, but will be different on your system!

C:\Users\<UserName>\AppData\Local\Apps\2.0\MP29KQC8.YHE\PYQ37ZO8.00A\be.e…dll_6ab7d7c3094fb0ee_0001.0000_none_c916387faa453a33

Known Installation Problems

Deployment and application do not have matching security zones

This error can occur during installation, if you are starting the setup.exe directly without extracting all files of the zip file. Download the Add-In and extract all files to your disc. After that you can execute setup.exe the follow the instructions for installation.

Advertisements

8 thoughts on “Sheet Switch AddIn for Excel 2010 – Switch between a lot of Sheets

  1. David R says:

    Is there a way to to switch it on and off?

  2. David R says:

    Wonderful, the configuration Q&A answered my question I was about to ask since default 5 is too few. I think 10 -12 might be a good one.
    Thanks very much for this excellent product again,

  3. David R says:

    Actually I have another questions, is it possible to add a pane on the left side of excel showing all the sheets like an explorer tree structure? and this pane can be turned on and off like an excel 2003 toolbar style?
    This is actually better than current one since we are now using more wide screen monitor.

    I know there is a tool that can realize this but it’s not free.

    I don’t know how difficult it is from technical point of view, just asking.

    • Thank you for your questions. At this moment I have no plan to provide further updates for the Add-In. If you know somebody having technical skills you can do what you want with the source code included in the download.

  4. Francois Beaupre says:

    I’ve got an error when installing : “Deployment and application do not have matching security zones”. Any idea how I can fix this ? This addin looks just too great to be true.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: