Reset Macro OnKey-Assignment to Default does not work for Recorded Macros

Question

In Excel 2016, a few days ago, by mistake I assigned a macro to Ctrl+y in the Macro-Recorder window.

Now, I would like to reset Ctrl+y to its default behaviour.

For this I found the following code:

Sub keys_deleteHotKeys()
   ' https://www.excelcampus.com/vba/keyboard-shortcut-run-macro/
    Application.OnKey "^y", ""
    Call Application.OnKey("^y")
End Sub

Unfortunately, the above Sub has no effect.

Does excel follow a different way of handling macro to key assignments when done in the Macro Recorder window as compared to a VBA Application.OnKey assignment?

Answer 1

The macro recorder always includes a hidden member attribute for the assigned hotkey, and there's nothing in the IDE to modify or otherwise manipulate it. You could export the code file, edit it (remove the hidden Attribute line), and re-import it into your VBA project... but that's quite a pain in the neck.

Get Rubberduck (free, open-source VBIDE add-in project that I manage), and then you can do this:

'@ExcelHotkey("y")
Public Sub MyAwesomeMacro()
    'do stuff...
End Sub

That annotation/comment can then be turned into the very same hidden member attribute Excel uses when you record macros and assign a hotkey (via static code analysis; see this post for all the details).

The lowecase "y" is what makes the hotkey Ctrl+y. If you want the hotkey to be Ctrl+Shift+y, simply make it a capital "Y":

'@ExcelHotkey("Y")
Public Sub MyAwesomeMacro()
    'do stuff...
End Sub

The advantages of this approach are numerous:

• No obscure hidden member attributes to manipulate.
• No need to export/edit/import anything.
• Obscure hidden member attributes are surfaced/documented.
• No need for any Application.OnKey hack.
• You get an inspection result whenever attributes & annotations disagree, and you get to synchronize the values with a single click.

If all you want is to remove the hotkey attribute, then simply run code inspections; Rubberduck will tell you when there's a hidden attribute without a corresponding annotation comment, and you can then decide whether to remove the attribute or add a corresponding annotation comment.

---

_{Note: the feature will work perfectly fine for OP's needs (removing the hidden attribute), but a regression bug was filed, since adding the hidden attribute is broken in v2.4.1.5170.}

Answer 2

In the macro list menu available from your Developer Ribbon tab, select your corresponding macro, then Option... and delete the shortcut you set.