Author Topic: ctrl + tab (first in, first out history) for excel sheets? (Read 33,303 times)

urlwolf · « **on:** March 30, 2007, 12:03 PM »

qst for excel ninjas...
is there any way (addin) to do ctrl + tab (first in, first out history) with excel sheets?

Any tips for navigating sheets? I find it a pain to have tabs on the bottom. No way to put them on top, or to the side in a tree form it seels.

I have seen this navigator, but why are excel addins so pricey?

A possibility would be some hack that converted all sheets to independent workbooks, (then you can ctrl tab through them). Later, it could merge them back to a single workbook.

Perry Mowbray · « **Reply #1 on:** March 30, 2007, 03:33 PM »

Try the attached.

I found the code at http://www.contextures.com/xlToolbar01.html

Unfortunately I added a sort routine before I realised you wanted "first in first out": sorry. You can always add the original code back in if you like, but I thought that a sorted list was better (especially if tabs are turned off).

Anyway, hope that helps,
Perry

Perry Mowbray · « **Reply #2 on:** March 30, 2007, 03:36 PM »

Quote from: urlwolf on March 30, 2007, 12:03 PM

but why are excel addins so pricey?

I think $30 is pretty standard for a professional Add-In (don't know how professional the one you mentioned is), probably because they're aimed at the business market: but there are lots of good free ones about too (although I couldn't find a ready-made menu for you).

- Perry

urlwolf · « **Reply #3 on:** March 30, 2007, 05:18 PM »

Thanks Perry, that is an interesting piece of code.
With a shortcut to get to the find box, it can make switching between sheets pretty snappy. I'd still prefer to have something like ctrl + tab, though.

The add-in I linked to shows worksheets as threes. Switching is by clicking. Not bad either. I think someone could do an emulation just changing the code you linked to (I don't think *I* would, since I know zero VBA).

I'll search a bit more, but definitely thanks for the hint!

kfitting · « **Reply #4 on:** March 31, 2007, 07:07 PM »

Try the code below. If it works as you want, I'll tell you how to get it to be present whenever you start excel (it's not intuitive and I don't know it off the top of my head... it involves making a personal.xls file and putting the following code in the workbook code module in an event function... if this works I'll step through that!)

Instructions:
- Open the Excel document you want to switch with ctrl-tab
- Press Alt-F11 to bring up the VBA window
- Click "Insert -> Module"
- Copy and paste the code below into the new module (it should be activated as soon as it's opened)
- Put the mouse cursor in the SetHotkey function (click anywhere in that section of code)
- Press F5 (this runs that function and sets ctrl tab to be a hotkey)
- Go back out to the main Excel window... and press... ctrl-tab....

Code: (vb)

Sub SetHotKey()
    Application.OnKey "^{TAB}", "SwitchSheet"

End Sub


Sub SwitchSheet()
    
    lngCurSheet = ActiveSheet.Index
    
    If lngCurSheet = ActiveWorkbook.Worksheets.Count Then
        Worksheets(1).Activate
    Else
        Worksheets(lngCurSheet + 1).Activate
    End If
End Sub

Perry Mowbray · « **Reply #5 on:** April 01, 2007, 10:49 AM »

Quote from: kfitting on March 31, 2007, 07:07 PM

Try the code below.

Nice work: this was my next attempt too

Quote from: kfitting on March 31, 2007, 07:07 PM

If it works as you want, I'll tell you how to get it to be present whenever you start excel (it's not intuitive and I don't know it off the top of my head... it involves making a personal.xls file and putting the following code in the workbook code module in an event function... if this works I'll step through that!)

You just make an Add-In (like the one I attached to my first reply, and you can either let Excel add it in automatically by saving it in ...\Application Data\Microsoft\AddIns (Depending on your windows version); alternatively you can save it anywhere and load it manually.

- Perry

Perry Mowbray · « **Reply #6 on:** April 01, 2007, 12:37 PM »

Hi: I've made a few changes to the original Add-In, as detailed below...

Quote from: urlwolf on March 30, 2007, 05:18 PM

Thanks Perry, that is an interesting piece of code.

I'm pretty adicted to it now: some of my workbooks have got quite a few sheets in them and if you expand the sheet tab area you loose scroller width.

Quote from: urlwolf on March 30, 2007, 05:18 PM

With a shortcut to get to the find box, it can make switching between sheets pretty snappy. I'd still prefer to have something like ctrl + tab, though.

I've added code so that clicking the "refresh" button is not necessary (so I removed the button)

I've also added other code to:

Navigate through WorkSheets of the open WorkBook [CTRL + TAB]
Navigate backwards through the History [CTRL + ALT + TAB]

kfitting posted almost the same code I wrote last night

, which navigates through the WorkSheets of the current WorkBook using CTRL + TAB keypress. I'm not sure if that's what you wanted or not?

Using CTRL + ALT + TAB will navigate backwards through the history of viewed WorkSheets.

As usual, to use this Add-In, load it through the Tools / Add-Ins menu.

- Perry

kfitting · « **Reply #7 on:** April 01, 2007, 05:30 PM »

Add-ins aren't the same as personal.xls... personal.xls is a workbook that (though not intuitive to create!) is opened every time excel is run and usually remains hidden. I've heard various pros and cons, but never really paid much attention since I usually make add-ins as well.

However, as I've stated before, anyone who uses excel ought to invest some time in VBA! It took 10 minutes or less to write the functions I wrote and there are so many times vba has made my work easier... even if it took 3 hours to do a ten second job!

One comment... why ctrl-alt-tab? Why not ctrl-shift-tab? Most of the programs that I've seen that use ctrl-tab add shift to go background. But, since it's in vba, it's easily customizable!

Kevin

urlwolf · « **Reply #8 on:** April 01, 2007, 08:56 PM »

Thanks kfitting, Perry,

Quote

One comment... why ctrl-alt-tab? Why not ctrl-shift-tab? Most of the programs that I've seen that use ctrl-tab add shift to go background. But, since it's in vba, it's easily customizable!

I agree, it'd be more interesting to mimic windows defaults.

I have magnaged to get some runtime errors. I deactivated all my other addins (that's how interested I am in this ctrl + tab functionality!), and have gotten rid of the runtime errors. However, the history doesn't work as expected (like normal windows ctrl + tab)... in my system, it just goes to next open sheet, not the last used. Example, imagine I have sheets A B C D E. I work on A, then click on C, and want to go back to A by doing ctrl + tab. The current implementation (at least in my system) goes to D (the SheetNumber ++), not to A.

Is it they way you intended it? (sorry, didn't look at the code!)

Perry Mowbray · « **Reply #9 on:** April 02, 2007, 08:02 AM »

Quote from: urlwolf on April 01, 2007, 08:56 PM

I have magnaged to get some runtime errors. I deactivated all my other addins (that's how interested I am in this ctrl + tab functionality!), and have gotten rid of the runtime errors. However, the history doesn't work as expected (like normal windows ctrl + tab)... in my system, it just goes to next open sheet, not the last used. Example, imagine I have sheets A B C D E. I work on A, then click on C, and want to go back to A by doing ctrl + tab. The current implementation (at least in my system) goes to D (the SheetNumber ++), not to A.

Are you using Navigate.xls?

If so, CTRL + TAB Navigates through your open worksheets (as mentioned above)
CTRL + ALT + TAB Navigates back through your recently viewed worksheets (I think as you're requesting)

The keypresses are certainly editable.

Last night I aslo implemented a forward as well as a backward navigate through your view history.

I'll pop it up later

Perry Mowbray · « **Reply #10 on:** April 02, 2007, 08:49 AM »

Hi Kevin:

Quote from: kfitting on April 01, 2007, 05:30 PM

One comment... why ctrl-alt-tab? Why not ctrl-shift-tab? Most of the programs that I've seen that use ctrl-tab add shift to go background. But, since it's in vba, it's easily customizable!

I agree, as set up in the last Add-In: CTRL+TAB navigated forward through the open WorkSheets; CTRL+ALT+TAB navigated backwards through the view history (different functionality). I was going to use CTRL+ALT+SHIFT+TAB to go forward through the backward navigated history.

But it's editable anyway.

- Perry

urlwolf · « **Reply #11 on:** April 02, 2007, 09:35 AM »

Hi Perry,

Yes, I'm using Navigate.xls, but not getting the "back to recently viewed worksheets" functionality. I get exactly the same functionality as when I do ctrl + page up/down. Is this what I should be getting?

Maybe there is some addin installed that is messing up with yours, even when turned off?

Thanks

Perry Mowbray · « **Reply #12 on:** April 02, 2007, 10:55 AM »

Quote from: urlwolf on April 02, 2007, 09:35 AM

Yes, I'm using Navigate.xls, but not getting the "back to recently viewed worksheets" functionality. I get exactly the same functionality as when I do ctrl + page up/down. Is this what I should be getting?

I think you're using CTRL+TAB not CTRL+ALT+TAB?

urlwolf · « **Reply #13 on:** April 02, 2007, 12:33 PM »

You are right

CTRL+ALT+TAB is a contorsion for me.
I'll try to change it on the code.
Then, how do I make this change "permanent" (i.e., opening with excel everytime)?

urlwolf · « **Reply #14 on:** April 02, 2007, 12:44 PM »

Hmm, testing now, it seems that it goes back in history, but that is not exaclty mimicking the typical alt + tab behavior in windows.

example. We have tabs A-C, and we click A, B, C. We are in C now, doing some work. I want to go back to B, so I hit ctrl tab. check some data, then I want to go back to C, and hit ctrl tab (as in windows alt tab). The current code takes me to A, not to C.

I think the history array should be updated after I hit ctrl tab (or as it is right now, CTRL+ALT+TAB). That way, the "last visited" tab will be the expected one.

I think there is a name for this kind of list in CS, not sure I remember the name (fifo?). STL in C++ has a template that implements exactly this....

Perry Mowbray · « **Reply #15 on:** April 02, 2007, 12:55 PM »

Quote from: urlwolf on April 02, 2007, 12:33 PM

You are right
CTRL+ALT+TAB is a contorsion for me.
I'll try to change it on the code.

No, don't worry, try the attached.

The penny's finally dropped that you don't want to cycle through the WorkSheets, so I've removed it

The Add-In is now set:

CNTL+TAB goes back in the view history
CNTL+SHIFT+TAB goes forward

I've also added a function that I find very handy: FollowReference. I don't know about you, but my sheets tend to reference lots of other cells. FollowReference will activate the referenced cell (if it can figure it out) or display the Precendants for you to choose.

I've also added buttons to the tool bar.

I've just a bit of a play, and everything seems to work without falling apart gracelessly

Most of the settings are at the top of the WorkHistory Module: HotKey combinations, number of "views" to remember.

Quote from: urlwolf on April 02, 2007, 12:33 PM

Then, how do I make this change "permanent" (i.e., opening with excel everytime)?

You just put it into your Excel Add-In directory, the location of which depends what version of Windows you are running:
For Excel 2007 for the PC, the default path to this folder is for Windows XP, and probably Vista:

Code

C:\Documents and Settings\UserName\Application Data\Microsoft\AddIns

For Excel 2003, XP and 2000 for the PC, the default path to this folder is for Windows 2000 or Windows XP:

Code

C:\Documents and Settings\UserName\Application Data\Microsoft\AddIns

or, if user profiles are used on your computer:

Code

C:\Windows\Profiles\UserName\Application Data\Microsoft\AddIns

or, for older Windows operating systems:

Code

C:\Windows\Application Data\Microsoft\AddIns

For Excel 97 for the PC, the default path is:

Code

C:\Program Files\Microsoft Office\Office\Library

Hope that helps, let me know if things don't work,
Perry

Perry Mowbray · « **Reply #16 on:** April 02, 2007, 01:00 PM »

Quote from: urlwolf on April 02, 2007, 12:44 PM

example. We have tabs A-C, and we click A, B, C. We are in C now, doing some work. I want to go back to B, so I hit ctrl tab. check some data, then I want to go back to C, and hit ctrl tab (as in windows alt tab). The current code takes me to A, not to C.

You're right, it's more like a Browser History.

So you browse from A > B > C,
press GoBack = B
press GoForward = C
press GoBack = B
press GoBack = A
press GoForward = B

Hmm, that's what made sense at the time $:-\$

urlwolf · « **Reply #17 on:** April 02, 2007, 01:38 PM »

Thanks, I'll make it permanent!
So I reckon you prefer the browser history paradigm, right?
I still like the "win alt tab" better.
Is this easy to implement?

Testing now your very handy FollowReference...

urlwolf · « **Reply #18 on:** April 02, 2007, 01:41 PM »

In fact, mostly any program that has tabs implements this kind of 'alt tab' behavior. Excel does have tabs, although they cannot be relocated.

I wonder why M$ didn't think of this before... weird. My browser works that way, my editor works that way... why do we have to do contorsions to get this basic functionality in excel?

Perry Mowbray · « **Reply #19 on:** April 02, 2007, 01:50 PM »

Quote from: urlwolf on April 02, 2007, 01:38 PM

Is this easy to implement?

I'm working on it... It's getting there...

urlwolf · « **Reply #20 on:** April 03, 2007, 12:00 AM »

Perry, for some reason, when I moved yout addin to C:\Documents and Settings\UserName\Application Data\Microsoft\AddIns, my 'other' favorite addin stopped working.

It's ASAP utilities:
http://www.asap-utilities.com/

Do you have any idea why?
Office 2007, win XP SP 2 here.

Perry Mowbray · « **Reply #21 on:** April 03, 2007, 02:10 AM »

Quote from: urlwolf on April 03, 2007, 12:00 AM

Perry, for some reason, when I moved yout addin to C:\Documents and Settings\UserName\Application Data\Microsoft\AddIns, my 'other' favorite addin stopped working.

Stopped working or did not open?

If it didn't open you can go to Tools > Add-Ins and select it, and it should open automatically in future.

You should be able to open as many as you like

But I don't have Office2007, but I'd think it should work the same.

I've also got the ALT+TAB working the way you'd like it to work (I think): now there's the best of both worlds:

ALT+TAB and ALT+SHIFT+TAB to navigate through Windows Style
Buttons to navigate through Browser Style

I'll upload it when I get home from work

Perry Mowbray · « **Reply #22 on:** April 03, 2007, 11:02 PM »

Quote from: urlwolf on April 03, 2007, 12:00 AM

Do you have any idea why?
Office 2007, win XP SP 2 here.

None at all: I can't install it here so it'll have to wait until I get home.

I've attached the new version of the Add-In. This includes:

CTRL+TAB: Windows style forward navigate through view history
CTRL+TAB+SHIFT: Windows style backward navigate through view history
Back Button: Browser style back through view history
Forward Button: Browser style forward through view history
Follow: Follow's References
Chase: Follow's Dependants (Cell's referencing selected cell)

See how you go...

urlwolf · « **Reply #23 on:** April 04, 2007, 01:04 AM »

Yes! This it it! perfect!
Some credits your way!
Thanks!

Perry Mowbray · « **Reply #24 on:** April 04, 2007, 01:42 AM »

Quote from: urlwolf on April 04, 2007, 01:04 AM

Yes! This it it! perfect!

Got there in the end $:-\$

It also will open WorkBooks that you've closed

Didn't mention that Chase Dependants button only automatically activates Dependants located on the current sheet (Excel restriction), otherwise it displays the links.

Did you sort out the issues with the other Add-In?

I couldn't install the new one (because of privileges), but the older one works fine: ie both together.