MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Allocating a hot key to a macro in VBA

Posted by David Bissland on October 28, 2001 5:20 AM

When you create a macro with the macro recorder, you have the option of attaching it to a hot key (Ctrl something).
Whats the code to do the same thing if you write the macro in VBA without using the macro recorder.

Posted by Tom Urtis on October 28, 2001 6:53 AM

When you are done writing your macro, go back to the worksheet menu and click Tools > Macro > Macros, then highlight your macro's name and click the "Options..." button. A macro options dialog box will appear with a field to allow you to enter your shortcut key there. As you might've guessed, you can also do this with macros that you recorded but did not assign a shortcut key to at the time of recording. This is also how you'd change the shortcut key after one has already been assigned.

Any help?

Tom Urtis

Posted by Tom Urtis on October 28, 2001 8:47 AM

One follow-up to previous post

Hey David,

I was watching one of the football pregame shows while posting the previous response and forgot to mention something. You can use the OnKey method to assign a macro shortcut key but it's a better idea to do it from the macro options dialog box.

To do it via the OnKey method, as an example, you can do the following:

Sub SetShortcutKeys()
With Application
.OnKey Key:="^+K", Procedure:="YourMacro"
End With
End Sub

This code defines Ctrl+Shift+K as the shortcut key to execute the YourMacro subroutine.

In my humble opinion, it's better to keep life simple, by assigning the shortcut key via the Options > Macro Dialog box, rather than writing a routine to do it, but at least you know you have a choice.

Hope all this helps.

Tom Urtis

Posted by David Bissland on October 28, 2001 9:30 AM

Re: One follow-up to previous post

Thanks Tom,

Thats exactly what I was after.

Have a great day