New and Improved Wrap Sheets Hotkey

Hey, remember back in 2010 when I had that bright idea about a repurposing Ctrl+PgUp and Ctrl+PgDn? Sure you do. The idea was that when I’m at the end of a workbook with a lot of worksheets, it would be easier to hit Ctrl+PgDn and wrap around to the first sheet rather than hold down Ctrl+PgUp until I got to the first sheet. I gave that shortcut the ol’ college try, but in the end I abandoned it. It turns out that I like holding down Ctrl+PgUp to get to the first page. But when I do that and this utility is active, it blows right by the first page and wraps around.

There’s this thing called Fitts’ Law and it indicates some things about infinity

Fitts’ law indicates that the most quickly accessed targets on any computer display are the four corners of the screen, because of their pinning action, and yet, for years, they seemed to be avoided at all costs by designers.

Fitts’ Law deals with pointing devices and targets, but the same principle applies to hotkeys. In Excel, if you hold down Ctrl+PgDn for infinity, you will end up on the last visible sheet of your workbook. That’s powerful because it removes all of your precision responsibility. I don’t care how imprecise you are, you can certainly hit an infinitely wide barn. Contrast that with the File menu in Excel 2010. Press Alt+F and then hold the down arrow for infinity. If the UI designers had been paying attention you would end up on “Exit” (the last item on the list), but you don’t. The cursor just keeps looping through the items on the list.

Even though I abandoned the hotkey, I can’t seem to get it out of my head that it’s a good idea. I’m stubborn like that. What if I could have the best of both worlds? What if I could race to the end of a workbook by holding down Ctrl+PgDn, but still wrap around to the first sheet when I wanted to? I changed the code to add a little delay. I started with 1 second, but determined that 1/2 second works better. Now, if the time between the last time I pressed Ctrl+PgDn and now is greater that 0.5 seconds, sheet activation will wrap around to the first sheet. If it’s less than 0.5 seconds, it’s assumed that I’m looking for infinity and remains on the last worksheet. And it all works the same for Ctrl+PgUp, just in reverse. I start with a module level variable and a module level constant.

Private msnLastWrap As Single
Private Const msnWRAPBUFFER As Single = 0.05

The Single msnLastWrap will keep track of the last time I pressed the hotkey. The rest of the code is the same from the prior post except that I added a couple of If statements to check the time differential and of course to set msnLastWrap.

Sub WrapSheetsUp()
           
    If ActiveSheet.Index = FirstVisibleSheetIndex Then
        If Timer - msnLastWrap > msnWRAPBUFFER Then
            ActiveWorkbook.Sheets(LastVisibleSheetIndex).Activate
        End If
    Else
        ActiveWorkbook.Sheets(NextVisibleSheetIndex(False)).Activate
    End If
   
    msnLastWrap = Timer
   
End Sub

Sub WrapSheetsDown()
   
    If ActiveSheet.Index = LastVisibleSheetIndex Then
        If Timer - msnLastWrap > msnWRAPBUFFER Then
            ActiveWorkbook.Sheets(FirstVisibleSheetIndex).Activate
        End If
    Else
        ActiveWorkbook.Sheets(NextVisibleSheetIndex(True)).Activate
    End If
   
    msnLastWrap = Timer
   
End Sub

In WrapSheetsDown when it gets to the last sheet (ActiveSheet.Index = LastVisibleSheetIndex) it checks to see how much time has elapsed (Timer – msnLastWrap). Timer is a VBA function that returns the number of seconds since midnight. Regardless of the results of that test, I assign Timer to my module level variable.

The rest of the code is unchanged, but for completeness, here’s what I have in my Auto_Open and Auto_Close procedures respectively.

    Application.OnKey "^{PGUP}", "WrapSheetsUp"
    Application.OnKey "^{PGDN}", "WrapSheetsDown"

    Application.OnKey "^{PGUP}"
    Application.OnKey "^{PGDN}"

And the rest of the procedures needed.

Public Function FirstVisibleSheetIndex() As Long
   
    Dim lReturn As Long
    Dim sh As Object
   
    For Each sh In ActiveWorkbook.Sheets
        If sh.Visible Then
            lReturn = sh.Index
            Exit For
        End If
    Next sh
   
    FirstVisibleSheetIndex = lReturn
   
End Function

Public Function LastVisibleSheetIndex() As Long
   
    Dim lReturn As Long
    Dim i As Long
   
    For i = ActiveWorkbook.Sheets.Count To 1 Step -1
        If ActiveWorkbook.Sheets(i).Visible Then
            lReturn = i
            Exit For
        End If
    Next i
   
    LastVisibleSheetIndex = lReturn
   
End Function

Public Function NextVisibleSheetIndex(bDown As Boolean) As Long
   
    Dim lReturn As Long
    Dim i As Long
   
    If bDown Then
        For i = ActiveSheet.Index + 1 To ActiveWorkbook.Sheets.Count
            If ActiveWorkbook.Sheets(i).Visible Then
                lReturn = i
                Exit For
            End If
        Next i
    Else
        For i = ActiveSheet.Index - 1 To 1 Step -1
            If ActiveWorkbook.Sheets(i).Visible Then
                lReturn = i
                Exit For
            End If
        Next i
    End If
   
    NextVisibleSheetIndex = lReturn
   
End Function

10 Comments

  1. Jeff Weir says:

    I don’t care how imprecise you are, you can certainly hit an infinitely wide barn.

    Not necessarily true: If I’m facing away from it, I hit an infinitely wide space. Granted, I would likely have been drinking, and really shouldn’t be handling firearms.

    I had assumed that the little triangles in the bottom left were for navigating through sheets i.e. the one that looks like this |< would take you to the first sheet and the one that looked like this >| would take you to the last. But then I clicked on it and it appeared to do nothing. So I fired up the macro recorder and clicked on it, and got this:

    ActiveWindow.ScrollWorkbookTabs Position:=xlLast

    …which Google tells me “scrolls through the workbook tabs to the last sheet in the workbook.”

    Not on my machine it doesn’t.

  2. Dick Kusleika says:

    It scrolls in that it makes them visible, it just doesn’t activate them as it scrolls

  3. Jeff Weir says:

    Ah yes of course…was trying it in a workbook with just 3 sheets. I don’t need an infinitely wide barn, because I have an irresistible foot.

    I’ve managed to condense your code down to two routines. Note the different Application.OnKey notations in the code comments.

    Option Explicit

    Private msnLastWrap As Single
    Private Const msnWRAPBUFFER As Single = 0.25

    'Trigger with these:
    'Application.OnKey "^{PGUP}", "'WrapSheets ""-1""'"
    'Application.OnKey "^{PGDN}", "'WrapSheets ""1""'"

    Sub WrapSheets(lngStep As Long)
               
        If ActiveSheet.Index = VisibleSheetIndex(lngStep, True) Then 'this will only be TRUE if we are already on the first or lowest sheet
            If Timer - msnLastWrap > msnWRAPBUFFER Then
                ActiveWorkbook.Sheets(VisibleSheetIndex(1, False) + VisibleSheetIndex(-1, False) - ActiveSheet.Index).Activate
            End If
        Else
            ActiveWorkbook.Sheets(VisibleSheetIndex(lngStep, True)).Activate
        End If
       
        msnLastWrap = Timer
       
    End Sub



    Public Function VisibleSheetIndex(lngStep As Long, bNext As Boolean) As Long
       
        Dim lReturn As Long
        Dim i As Long
        Dim lng As Long
        If lngStep = 1 Then
            lng = ActiveWorkbook.Sheets.Count
        Else: lng = 1
        End If
       lReturn = ActiveSheet.Index
            For i = ActiveSheet.Index + lngStep To lng Step lngStep
                If ActiveWorkbook.Sheets(i).Visible Then
                        lReturn = i
                        If bNext Then Exit For
                End If
            Next i
       
        VisibleSheetIndex = lReturn
       
    End Function
  4. Peter Albert says:

    Brilliant idea – will place this in my personal add-in straight away! Thanks!

  5. Peter Albert says:

    @Jeff: Two small corrections: Better use 0.5 (the routine takes longer than 0.05 on my machine) – and swap the signs for the OnKeys.

    Private Const msnWRAPBUFFER As Single = 0.5

    ‘Trigger with these:
    ‘Application.OnKey “^{PGUP}”, “‘WrapSheets “”-1″”‘”
    ‘Application.OnKey “^{PGDN}”, “‘WrapSheets “”1″”‘”

    Don’t know how to quote as VB, sorry. Could anyone please enlighten me? Thanks!

  6. Peter Albert says:

    Could resist to play a bit of code golf here: :-)

    Here’s my version:

    Option Explicit

    Private mDblLastWrap As Double
    Private Const mcDblWrapBuffer As Single = 0.5

    'Trigger with these:
    'Application.OnKey "^{PGUP}", "'WrapSheets True'"
    'Application.OnKey "^{PGDN}", "'WrapSheets False'"

    Sub WrapSheets(blnBackwards As Boolean)
       
        subActivateNextSheet blnBackwards, (Timer - mDblLastWrap > mcDblWrapBuffer)
       
        mDblLastWrap = Timer
       
    End Sub

    Private Sub subActivateNextSheet(blnBackwards As Boolean, blnAllowWrap As Boolean, Optional intIndex As Integer)
        If intIndex = 0 Then intIndex = ActiveSheet.Index
       
        intIndex = intIndex + IIf(blnBackwards, -1, 1)
       
        If intIndex = 0 Or intIndex = ActiveWorkbook.Worksheets.Count + 1 Then _
            intIndex = IIf(blnAllowWrap, Abs(intIndex - ActiveWorkbook.Worksheets.Count), ActiveSheet.Index)
       
        If ActiveWorkbook.Sheets(intIndex).Visible Then
            ActiveWorkbook.Sheets(intIndex).Activate
        Else
            subActivateNextSheet blnBackwards, blnAllowWrap, intIndex
        End If
       
    End Sub
  7. snb says:

    Dick,

    Can’t you add the [code lang=”vb”] [/code] anywhere near the reply ‘box’

    NB. the brackets in [code lang=”vb”] [/code] have to be replaced by respectively. Ih to use [ ] to prevent the interpreter to consideer it VB-code.

  8. Jeff Weir says:

    Hi all. Code tags for this blog use angle brackets, rather than square ones. Have added these to Peter’s comment above.

    Dick, can you add some instructions somewhere on what tags commenters can use?

  9. Jeff Weir says:

    Peter, code golf…nice term. Looks like you scored a hole in one to me. Awesome.

  10. Dick Kusleika says:

    I used to have instructions for posting code in comments, but they got lost. It is literally on my todo list and it’s about time I checked it off.

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: