assign a shortcut key AND hide macro

Question

I am at a dead end, i have been trying to hide all macros, I have been able to do all macros that do NOT have a short Key assigned by Private Sub, but whenever i use Private on a macro that has a shortcut key assigned it doesn't not work, in fact, it disables the shortcut key

I have tried

Private Sub Workbook_Open()
Application.OnKey "+Q", "Macro1"
End Sub

and that doesn't work, yes I placed it in This Workbook.....of course if i take Private out of the macro and run it works fine. I have tried ^+Q and that doesn't work either

there has to be a way, isn't there?

David Zemens · Accepted Answer · 2013-11-27 14:57:59Z

3

Two things:

First, if you need capital "Q" then you need to do:

Application.OnKey "^+Q", "Macro1"

Also, it isn't necessary to make the macros private in order to hide them from the ribbon/macros menu. Here is a workaround that I use:

Any macro/subroutine will be 'hidden' (not displayed in the list of macros from the Ribbon/menu) if it takes at least one argument. So one workaround for you is to just add an optional, meaningless argument for each subroutine, and then you can leave them as public subs.

Example:

Sub Macro1(Optional dummy)
    MsgBox "Hi!"
End Sub

The above macro should not appear in the list of available macros, but it should still work with your hotkey.

answered Nov 27, 2013 at 14:57

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2140173 Over a year ago

+1 great and specific workaround. Have you ever tried with hidden modules?

user2140173 Over a year ago

oh no nvm I got too carried away :)

Collectives™ on Stack Overflow

assign a shortcut key AND hide macro

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related