Shortcut

[Steph]

Is it possible to assign a shortcut keystroke (ie Ctl U) to a sub procedure
if it resides in a private module?

[Leith Ross]

Hello Steph,

If by private module you mean a module you inserted into your VBA project then the answer is yes. However, you can only assign a hot combination that isn't in use. This example calls the Sub (macro) "RunProgram" in such a module. The Sub must be Public for this to work. Place this code in ThisWorkbook under Excel Objects in your project in the Private Sub WorkBook_Open() procedure.

Example:
'Assign Keys CTRL + ALT + R to RunProgram Macro
Application.OnKey Key:="%^r", Procedure:="RunProgram"

Shift = "+", CTRL = "%", ALT = "^"

To learn more about Key Codes see SendKeys Method in the VB Help.


Sincerely,
Leith Ross

[Jim Thomlinson]

You can not assign a hot key to a private anything. If at the top of your
module you have...

option private module
then everything in the module is hidden adn the scope of everything in the
module is private.

If you have
Private Sub Test
then the scope of this procedure is private and once again you will not be
able to refernece it outside of the module.

By default whether explicitly stated or not everything else is Public. If
however your procedure takes arguments then you will not be able to assign a
hot key.

Public Sub Test
This is public and you can assign a hot key to it. Tools -> Macros -> Macros
-> Options...
--
HTH...

Jim Thomlinson


"Steph" wrote:

> Is it possible to assign a shortcut keystroke (ie Ctl U) to a sub procedure
> if it resides in a private module?
>
>
>