Shortcut Key Launch Short-circuits Macro

[Devin]

I have a bit of code that will work when launching via the ribbon (or from VB), but not will not work when launched via a shortcut key.

The activate tab box will pop up when launched via the shortcut key, but it won't go to the tab selected.

This is for the box that pops up when there are more than 16 tabs in the workbook.

What's going on?



<CODE>
'The Macro
Sub ShowSheetLists()
WC = Application.Worksheets.Count
If WC > 16 Then
Application.CommandBars("Workbook Tabs").Controls("More Sheets...").Execute
If Err.Number > 0 Then
Err.Clear
Application.CommandBars("Workbook Tabs").ShowPopup
End If
Else
Application.CommandBars("Workbook tabs").ShowPopup
End If
On Error GoTo 0
End Sub

'The shortcut key
Application.OnKey Key:="^+p", Procedure:="ShowSheetLists"

'The XLAM ribbon code
Sub ShowSheetListsR(control As IRibbonControl)
On Error Resume Next
Run "ShowSheetLists"
On Error GoTo 0
End Sub

<CODE/>

Thanks!!!!

 

[Devin]

Quick note regarding the shortcut code (not sure if it is relevant): It is in the "Microsoft Excel Objects" folder "ThisWorkbook".

The full code is:

<CODE>

Private Sub Workbook_Open()
Application.OnKey Key:="^+p", Procedure:="ShowSheetLists"
End Sub

<CODE/>

 

[Devin]

There is a thread on StackOverflow which seems to acknowledge the issue, but doesn't have a solution (excel - Why does this code work with F1 and not CTRL-M (for example) - Stack Overflow).

Apparently, using the CTRL key in the shortcut is the issue. Excel behaves as if the CTRL key is held down throughout the running of the macro and this messes things up.

Any solution to this?