keyboard shortcut to run Sub(module in VB)

**vandanavai** · 02-20-2007, 12:25 PM

Hello ,

I am finding this bit difficult to explain my problem. I have module which contains one Sub (VB code). I know that this can be run after opening VB by using Alt+F11 and then F5. But I want to run this without opeing VB i.e. just like we run recored macro by using keyboard shortcut assigned to it while recording it. Also I want to run this Sub in many files without requiring it to copy in each file.

Please help me

**Leith Ross** · 02-20-2007, 01:17 PM

Hello Vandanavai,

If your macro is a Sub then its easy to assign a shortcut key to your macro. While in Excel, type ALT+F8 to bring up the Macros List. Select the name of your macro by clicking it. At the bottom right corner of the dialog, click "Options..". You can then assign a shortcut key and add a description of the macro for other to see when it is selected in the Macro List.

The drawback is this will work only in the Workbook the macro is saved in. To have it available to any workbook requires adding the shortcut by code using the Application.OnKey method...

OnKey Method Example

This example assigns "InsertProc" to the key sequence CTRL+PLUS SIGN and assigns "SpecialPrintProc" to the key sequence SHIFT+CTRL+RIGHT ARROW.

Application.OnKey "^{+}", "InsertProc"
Application.OnKey "+^{RIGHT}", "SpecialPrintProc"
This example returns SHIFT+CTRL+RIGHT ARROW to its normal meaning.

Application.OnKey "+^{RIGHT}"
This example disables the SHIFT+CTRL+RIGHT ARROW key sequence.

Application.OnKey "+^{RIGHT}", ""

To learn more about the key codes see your VBA help file under "SendKeys". To make the macro available to other workbooks without saving the macro to them, save your macro to your "Personal.xls" workbook.

Sincerely,
Leith Ross

**vandanavai** · 02-21-2007, 01:53 AM

Thank you Lith Ross

Since I am not programmer , I found OnKey Method difficult to understand. Will you please guide further? You have give example. What I wanted to know is that if I want to assign say "Ctrl+a" as shortcut key for my "Sub" , how it is to be incorporated in my "Sub". Code give by you is to be included in my "sub"?

Please help me.

**Leith Ross** · 02-21-2007, 02:23 AM

Hello Vandanavai,

Shortcut keys are registered within the system by the application. The application program in this instance is Excel. When a shortcut key combination is saved in the list it is in Last In First Out (LIFO) order. This is why you can temporarily overide what has been set by Excel for menu item, toolbars, etc. I will include an attachment of key codes for your reference, but will provide the CTRL+a combination here in the example

Sub MyMacro()

Application.OnKey "^a", "PrintWorksheet"

End Sub

The "^" character is the special code for the CTRL key, "+" is for the shift key and "%" for the ALT key. The example defines CTRL+a to call the procedure "PrintWorksheet". You would replace this string with the name of the procedure you would be calling.

Sincerely,
Leith Ross