Is there a “see all sheets” keyboard shortcut in Excel 2013 on Windows?

Question

When I right-click the arrow buttons on the bottom left corner of Excel, a window listing all sheets will be displayed (as shown below):

[

I couldn't find any keyboard shortcut to display this, neither could I declare a new shortcut linked with this window. Is there any shortcut for this option?

Answer 1

I don't believe there is a direct shortcut for that dialog.

You can get similar by using a small bit of VBA in a macro, and then assigning it to a keyboard shortcut of your choice.

Sub ShowAllSheets()
  Application.CommandBars("Workbook tabs").ShowPopup
End Sub

• Source
• More Info

Answer 2

Similar to the correct answer by Techie, here’s a solution that works when you have >15 sheets (and also makes it possible to click a letter to highlight sheet starting with that letter):

Sub Select_Sheet()
If ActiveWorkbook.Sheets.Count <= 16 Then
Application.CommandBars("Workbook Tabs").ShowPopup 500, 225
Else
Application.CommandBars("Workbook Tabs").Controls("More Sheets...").Execute
End If
End Sub

Although for unknown reason it does not work when activating using keyboard (as mentioned in Source and another example that also dsnt work when using shortcut is here). If someone knows how to fix the code above to work with macro please feel free to fix it.

Answer 3

I'd like to reply to Stenemo but this is a new account and I need 50 reputation or something.

Anyway, this works:

Sub Sheet_Index()

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

Source: https://www.reddit.com/r/excel/comments/383b3z/keyboard_shortcut_for_listing_all_the_worksheet/crs7pah/?utm_source=share&utm_medium=web2x

Cheers.

Edit for some reason if I use this with a keyboard shortcut it turns on and off my num lock every time it's run.