DonationCoder.com Forum

Main Area and Open Discussion => General Software Discussion => Topic started by: urlwolf on March 30, 2007, 07:03 AM

Title: ctrl + tab (first in, first out history) for excel sheets?
Post by: urlwolf on March 30, 2007, 07:03 AM
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 (http://www.ablebits.com/excel-worksheets-manager-addins/), 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.
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: Perry Mowbray on March 30, 2007, 10:33 AM
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
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: Perry Mowbray on March 30, 2007, 10:36 AM
but why are excel addins so pricey?
-urlwolf (March 30, 2007, 07:03 AM)

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
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: urlwolf on March 30, 2007, 12: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!
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: kfitting on March 31, 2007, 02: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: Visual Basic [Select]
  1. Sub SetHotKey()
  2.     Application.OnKey "^{TAB}", "SwitchSheet"
  3.  
  4. End Sub
  5.  
  6.  
  7. Sub SwitchSheet()
  8.    
  9.     lngCurSheet = ActiveSheet.Index
  10.    
  11.     If lngCurSheet = ActiveWorkbook.Worksheets.Count Then
  12.         Worksheets(1).Activate
  13.     Else
  14.         Worksheets(lngCurSheet + 1).Activate
  15.     End If
  16. End Sub
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: Perry Mowbray on April 01, 2007, 05:49 AM
Try the code below.
-kfitting (March 31, 2007, 02:07 PM)

Nice work: this was my next attempt too  :)

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!)
-kfitting (March 31, 2007, 02:07 PM)

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
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: Perry Mowbray on April 01, 2007, 07:37 AM
Hi: I've made a few changes to the original Add-In, as detailed below...

Thanks Perry, that is an interesting piece of code.
-urlwolf (March 30, 2007, 12:18 PM)

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.

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.
-urlwolf (March 30, 2007, 12:18 PM)

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:

kfitting (https://www.donationcoder.com/forum/index.php?topic=7932.msg56181#msg56181) 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
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: kfitting on April 01, 2007, 12: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
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: urlwolf on April 01, 2007, 03:56 PM
Thanks kfitting, Perry,

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!)
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: Perry Mowbray on April 02, 2007, 03:02 AM
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.
-urlwolf (April 01, 2007, 03:56 PM)

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


Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: Perry Mowbray on April 02, 2007, 03:49 AM
Hi Kevin:

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! 
-kfitting (April 01, 2007, 12:30 PM)

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
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: urlwolf on April 02, 2007, 04: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
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: Perry Mowbray on April 02, 2007, 05:55 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?
-urlwolf (April 02, 2007, 04:35 AM)

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

Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: urlwolf on April 02, 2007, 07:33 AM
You are right  :D
 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)?
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: urlwolf on April 02, 2007, 07:44 AM
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....

Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: Perry Mowbray on April 02, 2007, 07:55 AM
You are right  :D
 CTRL+ALT+TAB is a contorsion for me.
I'll try to change it on the code.
-urlwolf (April 02, 2007, 07:33 AM)

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:

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.

Then, how do I make this change "permanent" (i.e., opening with excel everytime)?
-urlwolf (April 02, 2007, 07:33 AM)

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:
[Select]
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:
[Select]
C:\Documents and Settings\UserName\Application Data\Microsoft\AddIns

or, if user profiles are used on your computer:
[Select]
C:\Windows\Profiles\UserName\Application Data\Microsoft\AddIns

or, for older Windows operating systems:
[Select]
C:\Windows\Application Data\Microsoft\AddIns

For Excel 97 for the PC, the default path is:
[Select]
C:\Program Files\Microsoft Office\Office\Library

Hope that helps, let me know if things don't work,
Perry
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: Perry Mowbray on April 02, 2007, 08:00 AM
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.
-urlwolf (April 02, 2007, 07:44 AM)

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  :-\
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: urlwolf on April 02, 2007, 08:38 AM
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...
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: urlwolf on April 02, 2007, 08:41 AM
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?
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: Perry Mowbray on April 02, 2007, 08:50 AM
Is this easy to implement?
-urlwolf (April 02, 2007, 08:38 AM)

I'm working on it... It's getting there...
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: urlwolf on April 02, 2007, 07:00 PM
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.
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: Perry Mowbray on April 02, 2007, 09:10 PM
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.
-urlwolf (April 02, 2007, 07:00 PM)

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  :huh: 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:

I'll upload it when I get home from work  ;)
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: Perry Mowbray on April 03, 2007, 06:02 PM
Do you have any idea why?
Office 2007, win XP SP 2 here.
-urlwolf (April 02, 2007, 07:00 PM)

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:

See how you go...
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: urlwolf on April 03, 2007, 08:04 PM
Yes! This it it! perfect!
Some credits your way!
Thanks!
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: Perry Mowbray on April 03, 2007, 08:42 PM
Yes! This it it! perfect!
-urlwolf (April 03, 2007, 08:04 PM)

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.
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: cemole on April 04, 2007, 05:52 AM
I'm not sure whether it will be useful for anybody (I had to fish for it a little) but if you want to remove the arrows on the sheet once you clicked "Chase", you need to go to Tools > Formula Auditing > Remove All Arrows. Partial arrow removal option is also possible if you open the "Formula Auditing Toolbar".
(edited thanks to Perry Mowbray)
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: Perry Mowbray on April 04, 2007, 06:17 AM
I'm not sure whether it will be useful for anybody (I had to fish for it a little) but if you want to remove the arrows on the sheet once you clicked "Chase", you need to go to Tools > Formula Auditing > Remove All Arrows. I don't know a partial arrow removal option.
-cemole (April 04, 2007, 05:52 AM)

Ah, yes: Sorry...

You can open the Auditing Tool bar, and on that Toolbar there are a number of functions that will both add Precedents and Dependants for the selected Cell and also remove them as well. There is also the "Remove All" function as well.

I was going to add a Clear button to Remove All, but I thought that was just duplication of existing functions. What the Chase or Follow does activate the referenced cell (if possible).

- Perry
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: urlwolf on April 04, 2007, 08:28 AM
One question.
Is there any way to switch workbooks, now that ctrl + tab has been overriden?
You can always go to view > switch windows (xl 2007) for a list, but that is pretty inconvenient.

Maybe an extra shortcut such as ctrl shift tab would be a good addition? Is there any other native xl shortcut?
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: Perry Mowbray on April 04, 2007, 09:08 PM
One question.
-urlwolf (April 04, 2007, 08:28 AM)
Three question marks  ;)

Is there any way to switch workbooks, now that ctrl + tab has been overriden?
You can always go to view > switch windows (xl 2007) for a list, but that is pretty inconvenient.
-urlwolf (April 04, 2007, 08:28 AM)

I don't have 2007, so mine's under Window.

Maybe an extra shortcut such as ctrl shift tab would be a good addition? Is there any other native xl shortcut?
-urlwolf (April 04, 2007, 08:28 AM)

I had CTRL+SHIFT+TAB to go backwards through the view history (same as ALT+TAB & ALT+SHIFT+TAB in windows)

I don't know of any other native shortcut (apart from CTRL+TAB) and couldn't find one just now, but adding it through code would be extremely easy. In fact it might also be good as a Combo dropdown box as well (though it'd just be duplicating the menu... except that it could remember previously opened workbooks and open them again as well. Sort of a combination of Window + Recent File menu).

Do you have a preference for a Key Combination? I'd lean toward CTRL+ALT+TAB.

I think the ability to remember recently open WorkBooks in the current session is a good idea (I could never see the point of restricting it to 9), and a KeyPress to navigate through that list could work in a similar fashion (so that it's not scrolling but rather choosing)

- Perry
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: urlwolf on April 09, 2007, 07:22 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.

I can confirm that this is still happening. All addins are dissabled if a drop navigator.xls in that folder (!). Weird, no idea what excel 2007 may be doing different...
Do you have a preference for a Key Combination? I'd lean toward CTRL+ALT+TAB.
Looks good to me :)
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: Perry Mowbray on April 10, 2007, 07:55 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.

I can confirm that this is still happening. All addins are dissabled if a drop navigator.xls in that folder (!). Weird, no idea what excel 2007 may be doing different...
-urlwolf (April 09, 2007, 07:22 AM)

So just to confirm...


Odd: I've just installed it at home and everything works OK??

You can install Navigator somewhere else and add it manually as well. Have you tried that??

Weird is all I can say...

I'm working on the other stuff (after an Easter weekend unplugged  :) )
Title: Re: ctrl + tab (first in, first out history) for excel sheets?
Post by: Perry Mowbray on April 13, 2007, 05:43 AM
OK: here is a new version of the Navigator Add-In for MicroSoft Excel

What's New:

All the Key Shortcuts can be edited in the Code easily if what I've set doesn't suit...

I'm happy to add any other Navigation type functions  :)

Let me know if there any problems

- Perry