Wrap Sheets Hotkey

I use Control+PageUp/PageDown to navigate between sheets. Sometimes I need to get from the first sheet to the last sheet and I don’t want to hit the hotkey seven or eight times to get there. I recently added some code to my UIHelpers addin. First, I set up and destroy the hotkeys in Auto_Open and Auto_Close (it’s the last two).

Next I use the following code to move between sheets.

If I’m on the first sheet and Control+PageUp, it goes to the last. If I’m not on the first sheet, I tried to replicate the existing function of that hotkey. I may have missed something, but it’s worked so far. Here are the helper functions

One downside to this is the loss of the “infinity space”. That is, if you hold down control and hold Page Up (to infinity) you’ll eventually get to the first sheet and stop. But I think it’s worth it. We’ll see.

6 thoughts on “Wrap Sheets Hotkey

  1. I use Ctrl Page Down / Up as well because it is so useful.

    I was curious as to why you don’t use the right click on the navigation arrows at the bottom left of the worksheet to display a list of all the visible worksheets?

    That would still allow you to use Ctrl Page Up / Down to shift by 1 sheet.

  2. Alan: I’m not much of a mouse guy (understatement of the year). I use the mouse, I just limit it whenever possible. I feel I’m more efficient when my hands don’t leave the keyboard.

    I once tried to make a userform so I could switch sheets using only the keyboard, but I found that Ctl Pgup/PgDn is so ingrained that I never used it.

  3. Unfortunately the right-click thing is pretty useless for many-tabbed files (where this would actually be useful) as it only shows 12(?) tabs in its menu. I’m guessing this is one of the many things in Excel that was hard-coded back when 640×480 screens were the norm…

  4. David,

    It shows all the visible sheets,with the remainder under {More sheets} – click on that and you get a display window with a slider that allows you to find the sheet that you want. Not perfect but useful if you want to find a specific sheet.

  5. Yeah — and the “More” dialog that pops up lists the same 12 tabs, and is not resizable, so you have to scroll down to find any later sheet. So of little use unless you’re going to an early tab.

    Inspiration: I might make a macro to push me half-way through the tabs, either left or right, to very quickly get me to a tab at least near to the one I’m after. Think of it as a binary search — it would scale very well to files with many tabs, like the 70-tab monster I spend much of my working life in!

  6. I have 3 shortcuts in my Home Addin

    Ctrl+T = takes me to the Top sheet (1st visible sheet)

    Ctrl+E = takes me to the End Sheet (last visible sheet)

    Ctrl+Q = Displays a list of sheets if there are <=16 or displays the Activate Dialog if there are more than 16
    The good thing about the activate dialog is if you type the first few letters of a sheet name, it searches for sheets that match what you type

    Sub Sheet_Index()
    ‘Assigned to Ctrl+Q
    If Application.CommandBars(“workbook tabs”).Controls(16).Caption Like “More Sheets*” Then
    Application.ScreenUpdating = False
    If WINDOWS_VER > 5 Then
    If Application.Version = “12.0” Then
    Application.SendKeys “{end}~”
    Application.CommandBars(“workbook tabs”).ShowPopup
    Else
    Application.SendKeys “{end}~”
    Application.CommandBars(“workbook tabs”).Controls(16).Execute
    End If
    Else
    Application.SendKeys “{end}~”
    Application.CommandBars(“workbook tabs”).ShowPopup
    End If
    Application.ScreenUpdating = True
    Else
    Application.CommandBars(“workbook tabs”).ShowPopup
    End If

    Application.ScreenUpdating = True
    End Sub


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.