How to identify a phantom Excel VBA routine that uses keystroke shortcut

[TomBarsh]

I have an Excel workbook with 303 VBA routines in it. There is a "phantom" routine that I just found uses the keystroke shortcut: Ctrl + r. This file has been around for 20+ years so who knows when/where/why I set that up. I sure don't.

The phantom routine is capable of running as soon as the workbook is opened. I didn't really notice this particular routine until I started using the same shortcut keys for some new routines.

I want to find this "phantom" VBA routine but I don't want to look at all 303 entries in the "Run a Macro" dialog (I have started).

Is there a way to audit the shortcut keys for each VBA routine? Is there a way to use VBA to reset all the keystroke shortcuts in this file to ""?

I actually found a few routines on the web on how to compile a list of VBA routines. The only one I got working was this:

https://msdn.microsoft.com/en-us/library/dd890502(v=office.11).aspx

This works for me after a couple simplifications (although I haven't yet gone through the gory details yet). But I don't see how to audit or change the shortcut keys.

In fact, it seems that the shortcut keys are different technology, per Help they are Excel4 macro shortcuts ("Returns or sets the shortcut key for a name defined as a custom Microsoft Excel 4.0 macro command. Read/write String."). So I don't know if it's even possible to audit or change these via VBA. Except by pushing keystrokes.

Any tricks or tips?

 

[TomBarsh]

For example, I use these routines to set the shortcut when the particular sheet is activated, then remove the shortcut when the sheet is no longer active:

Private Sub Worksheet_Activate()
Application.MacroOptions Macro:="Sheet62.bentonite", Description:="", ShortcutKey:="r"
End Sub
Private Sub Worksheet_Deactivate()
Application.MacroOptions Macro:="Sheet62.bentonite", Description:="", ShortcutKey:=""
End Sub

 

[cowski]

I've not tried it, but perhaps this will help?

http://excel.tips.net/T003162_List_of_Macro_Shortcuts_in_All_Open_Workbooks.html

 

[TomBarsh]

Thanks! That would certainly do the trick. The link to the file seems to be "dead". But the idea gives me some direction for Google searching and brainstorming.

 

[TomBarsh]

This routine given at the Microsoft forums (usenet) seems to use the same approach, and the code is listed there.

https://groups.google.com/forum/#!topic/microsoft.public.excel.worksheet.functions/TwcT-IlWjVk

 

[IDS]

Tom - the comments to the original article give an alternative download site, which still works:

http://web.archive.org/web/20040302213139/http://www.xcelfiles.com/GetShortCutKeys.html

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/