Shortcut (Hotkey) to bring up Activate Sheets / More... Dialog Box

[tylertul]

Hello . I have looked all over the place and can't find the answer to this. I have tried recording a macro but is will only record the actual sheet I choose to activate and doesn't bring up the dialog box so that I can choose from the list of all of my sheets by simply typing the first few letters.

Normally one right clicks on the far bottom right of the screen on the left and right arrows to bring up a sheet chooser and then there is the more... option at the bottom if you have many sheets. I then have to click on more and I am where I need to be.

How can I get to this more... screen with just a shortcut (hokey). I'm sure it will require a macro but I don't know the code to get there. Thanks very much for any assistance. (Excel 2003)

 

[tylertul]

bump

 

[tylertul]

Got it! [FONT=Verdana, Arial, Helvetica]

Application.CommandBars("Workbook Tabs").ShowPopup [/FONT]

 

[Datsmart]

Thank you for sharing your solution.
I was watching this thread to see what someone came up with. I use that menu regularly and having code to activate it will be useful.

 

[Noble Joe]

What you guys are writting about sounds fascinating, I'd like to understand a bit more - could you explain it a bit more for us novices to understand?! thanks!

 

[tylertul]

Paste this into a standard module. I did not write this code. I found it on the web. It works well for me. You will then want to assign a shortcut to the macro. Look around for how to do it.

Sub Sheet_Index()
'I have the below macro assigned to a shortcut key - Ctrl+q
'It shows a very interesting thing….
'The behaviour of SendKeys is different not only between 2003 and 2007 but also between XP and VISTA….

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

 

[postjo]

Thanks! This is awesome. I've been searching for this forever.

 

[mw998]

Thank you. Just amazing, been looking for this for years. ​It was my Moby ****.

 

[jmdesk20]

Sorry for necromancing, but I've been looking to implement a shortcut for the Activate Sheet window as well, and stumbled upon this thread while searching for an answer. I'm pretty novice at most things Excel, so could someone explain how to take the code provided by @tylertul and actually form the macro out of out? I have figured out how to get the VBA and module page open, but that's about it.

How do I implement this? And from what I can see of the code, where do you insert your desired hotkey in order to assign it?

Many thanks in advance.

Excel 2019