Charlie Jans wrote an addin for quickly switching between Excel worksheets. I’ve been using it for the last few days and I love it. For me, this is one of those addins that provides functionality that should be included in the program.
Charlie has graciously allowed me to make the addin available. You can download SheetSwitcher.zip. The zip file contains one xla file that you load like a normal addin.
To switch between sheets, use the Alt+’ key combination. It’s designed to work similar to, but not exactly like, Alt+Tab does for switching between windows application. Charlie writes that he has worked out some of the international issues that he had in his first version, but if you try it out and have any problems, I’m sure he’d like to hear about them.
The key combination is Alt plus the backward apostrophe. It’s on the same key as the tilde(~) and just to the left of the 1 key on my keyboard.
Update: Thanks to Jeremiah’s comment, I decided to add a little more to this post. (I’m not sure why I was keeping it secret:))
Before this addin, I used Control+PgUp/PgDown to switch between sheets and Control+Tab to switch between workbooks. SheetSwitcher allows you to switch between sheets regardless of the workbook. Also, the addin orders the sheets in the order you last visited them, so going back to the last sheet you visited that was in a different workbook is one keystroke away instead of several. You can only go back five sheets. Charlie made a conscious decision to limit the number of sheets available, balancing the convenience of having all the sheets available with how ugly and unusable the userform would be in that case. I think it was good choice. Thanks for the comment, Jeremiah.
How is this different from Ctrl+PageUp or PageDown, am I missing something?
Maybe I should download and find out myself.
that moves the worksheets:
I think it may depend on the OS/Office version you have, and maybe how they are configured, but i can certianlly use Shift+tab to move though my work books [xp, xp combo]
thanks for this tool
CTRL-F6 is another shortcut to cycle thru open workbooks, even tho on a normal keyboard it requires some digit gymnastics
i use an Avant Stellar keyboard with function keys on the left as well as top…i reprogram the shift lock (left of the A key) as the CTRL key…now the CTRL is directly to the right of the F6 and it’s amazingly simple to cycle thru workbooks
Mike
I’ve always used Ctrl+Tab to flick through the open windows. Ctrl+Shift+Tab to flick in reverse.
It’s not easy to get the the correct window without trial + error.
this one? no… this one? no… this one? yes!
The add-in makes that job a lot easier!
One suggestion would be to remove the userform titlebar. I think Stephen Bullen’s FormFun is an example.
Cheers,
Rob
My name in lights! Thanks Dick.
I’m keen to improve this so please let me know if you have any comments everyone. The code is unprotected to feel free to play around with it.
Kind regards
Charlie
How do I change the shortcut used? I have another add-in which uses Ctrl+’ as it’s shortcut!
Hi Darren,
To change the shortcut requires several code changes.
To change to Alt+q
on line 121 of ufSwitcherWindow, change:
If KeyCode = 223 Or KeyCode = 192 Then
to:
If KeyCode = 81 Or KeyCode = 113 Then
and in ThisWorkbook, line 20, change:
Application.OnKey “%'”, “ShowForm”
to:
Application.OnKey “%q”, “ShowForm”
Alt + ‘ seemed like a good shortcut as it’s ergonomically convenient and underused in standard Excel. I did consider building an setup options stage on install, but decided that was just one more thing to potentially go wrong. Maybe I should rethink.
Kind Regards
Charlie
it gives an error
project or library not found
in the procedure it stops at a word “right”
my os is windows98 and excel 2000
second time excel crashes.
r.venkataraman,
A couple of things to check:
1) in the VBE, on the Tools -> Options menu, click the ‘General’ tab, and check that ‘Break on unhandled errors’ is selected.
2) I’ve just noticed there are a few redundant object library references left in there. In the VBE, Select Tools -> References and enter the password ‘a’ (without the quotes). You can remove every reference that doesn’t
give you an ‘in use’ message when you uncheck it.
If neither of those two work, I’m afraid I’m stumped. ‘Right’ should be standard functionality in all excel versions. Apologies for crashing your Excel, I’ve not seen this error occur before.
Regards
Charlie
I found that the difference between how one switches between workbooks and worksheets within a book was driving me crazy. So, I integrated the two in an add-in called Workbook Navigator. It’s at http://www.tushar-mehta.com/excel/software/utilities/wb_nav.html
hmmm, seems it’s not the shortcut that is similar. After changing the shortcut it still loads up the form from the other add-in!?
Charlie, R.Venkataraman –
Often when one reference is broken, another seemingly independent library seems to be affected. I had problems with different versions of SOLVER recently, which manifest in errors with the Strings library.
You can temporarily deal with the problems by inserting the prefix “Strings.” in front of any Strings library element:
Strings.Right()
Strings.Mid()
In the long term, you need to fix the problem, which means find broken references, as Charlie describes, or scratch your head a little harder.
– Jon
Tushar –
Looks more interesting to me than having to pop up a userform. I’ll let you know how I like it.
– Jon
Darren,
Most probably this is because of a duplicate procedure name in both addins.
In the code for ThisWorkbook, change the OnKey setting to:
Application.OnKey “%'”, “SheetSwitcher.Switcher.ShowForm”
(all one line)
This is something I really should have done myself, but it just never occurred to me. I guess that’s how you learn.
Kind regards
Charlie
Tushar –
I like the way your worksheet navigator works. The way I’d improve on it is to have a menu item for navigation, with the workbooks listed as menu items.
– Jon
Hi Jon,
Version after next. ;-) My first priority is to make the user settings and active status persistent across an XL shutdown. In the meantime, consider using the ‘Nest windows’ option and dock the commandbar adjacent to one of the commandbars at the top. Personally, though, I prefer the left bottom — to the left of whatever XL-native commandbar I have open at the bottom (usually, the Drawing toolbar).
[…] SheetSwitcher AddInHow is this different from Ctrl+PageUp or PageDown, am I missing something?http://www.dailydoseofexcel.com/archives/2005/01/18/sheetswitcher-addin/Entering Data into Excel – Entering Data into Excel Spreadsheets …Ctrl + Page Down keys: moves the […]