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).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
Sub Auto_Open() Application.OnKey "^%{DOWN}", "SelectAdjacentCol" Application.OnKey "+^%{RIGHT}", "FillSeries" Application.OnKey " ^ m", "MakeComma" Application.OnKey "^;", "IncrementDate" Application.OnKey "^+;", "DecrementDate" Application.OnKey " ^ v", "CopyPasteValues" Application.OnKey "^1", "ShowFormatting" Application.OnKey "^{PGDN}", "WrapSheetsDown" Application.OnKey "^{PGUP}", "WrapSheetsUp" CreateToolbars End Sub Sub Auto_Close() Application.OnKey "^%{DOWN}" Application.OnKey "+^%{RIGHT}" Application.OnKey " ^ m" Application.OnKey "^;" Application.OnKey "^+;" Application.OnKey " ^ v" Application.OnKey "^1" Application.OnKey "^{PGDN}" Application.OnKey "^{PGUP}" DeleteToolbars End Sub |
Next I use the following code to move between sheets.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Sub WrapSheetsUp() If ActiveSheet.Index = FirstVisibleSheetIndex Then ActiveWorkbook.Sheets(LastVisibleSheetIndex).Activate Else ActiveWorkbook.Sheets(NextVisibleSheetIndex(False)).Activate End If End Sub Sub WrapSheetsDown() If ActiveSheet.Index = LastVisibleSheetIndex Then ActiveWorkbook.Sheets(FirstVisibleSheetIndex).Activate Else ActiveWorkbook.Sheets(NextVisibleSheetIndex(True)).Activate End If End Sub |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
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 |
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.
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.
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.
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…
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.
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!
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
‘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