Allocating a hot key to a macro in VBA

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

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

Re: One follow-up to previous post

Thanks Tom,

Thats exactly what I was after.

Have a great day

David