How to identify what a shortcut key is triggering in an Excel workbook?

Question

I've inherited a workbook and a personal macro workbook that operate in conjunction with each other (drawing on a CSV and 2 LST files) to produce a report. The spreadsheet has rudimentary instructions, essentially:

1. Sign on to the server
2. Place the 3 (CSV and LST files) in the requisite subdirectory
3. Open the report template
4. Save the report template
5. Open the personal macro workbook
6. Hit "Ctrl+J"
7. Follow the prompts
8. Save and distribute the report

I open the files as required (with macros enabled) and followed the instructions but when I hit "Ctrl+J" nothing happens.

I searched through the VBA for both workbooks and can't find the command for "Ctrl+J" (or onkey or similar)

The original "developer" has left the organization so I am unable to speak to them.

Any suggestions on a place to start? Or alternatively h a way to trip all VBA code to notepad to search hidden depths on the workbook?

Thanks in advance.

Answer 1

When you record a macro using the Macro Recorder, you get to map a hotkey:

When you then look at the generated code, what you can see in the editor might look like this:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+J
'
    ...code...
End Sub

That's nice, but then if you deleted that comment, you can't quite know what the hotkey is anymore.

If you export that module and open it in Notepad, you'll see what the editor isn't showing you:

Sub Macro1()
Attribute Macro1.VB_ProcData.VB_Invoke_Func = "J\n14"
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+J
'
    ...code...
End Sub

This is the magic bit:

Attribute Macro1.VB_ProcData.VB_Invoke_Func = "J\n14"

All Excel hotkeys involve the Ctrl key. The capital J implies the use of the Shift key, and if you changed that attribute value to "e\n14" then the hotkey would become Ctrl+e.

Importing/exporting modules in the VBE is a royal pain in the neck. Rubberduck and its code inspections can help you there:

Every single instance of this particular inspection result (for an Invoke_Func hidden attribute) means you're looking at a macro that is assigned a hotkey.

After right-clicking that inspection result and selecting "Add missing attribute annotation", the code module now looks like this:

'@ExcelHotkey J
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+J
'
    ...code...
End Sub

Now you can delete the macro-recorder generated comment, and let Rubberduck synchronize the @ExcelHotkey annotation with the appropriate hidden attribute value - all without leaving the VBE or exporting/importing anything.

Changing the annotation to @ExcelHotkey e would change the hotkey from Ctrl+Shift+J to Ctrl+e, after synchronizing attributes and annotation comments with Rubberduck.