CODEWITHSUNDEEP
excelvbakeyshortcut
user2555568
user2555568

Reputation: 21

assign a shortcut key AND hide macro

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?

Upvotes: 2

Views: 2021

Answers (1)

David Zemens
David Zemens

Reputation: 53623

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.

Upvotes: 3

Related Questions