How to make a keyboard shortcut?

[Lea]

I would like to make a keyboard shorcut for a macro that I have. I would like it to be Alt+g. Does anyoone know how I can do this? If I go to Tools, Macro, Macros, the Options button only gives me the option to do Ctrl+.
Thanks.

 

[RoryA]

You can use Application.Onkey for this:

Sub SetKey()
   Application.OnKey "^g", "macro_name" '<---change macro name!
End Sub
Sub ResetKey()
   Application.OnKey "^g"
End Sub

You can use the Workbook_Open event to call the SetKey routine, and the Workbook_BeforeClose event to reset the key combination.
HTH

 

[iliace]

This line of code will cause Alt+G to run myMacro:

  Application.OnKey "%g", "myMacro"

You can put that in your workbook_open or workbook_activate event. In the opposite event (workbook_close or workbook_deactivate) you can use this line to unbind the key combination:

  Application.OnKey "%g"

 

[Lea]

rorya,
Thank you!
Lea

 

[Lea]

iliace,
Thank you...
Lea