Making macros work from shortcut keys

Posted by Wendy on April 12, 2001 8:44 AM

I want to write VBA code that will assign my macro to a shortcut key to another macro when I open the spreadsheet and de-assign it when I leave.
Can this be done?
Thanks, Wendy

Posted by Dave Hawley on April 12, 2001 10:39 AM

Hi Wendy

It sure can. Right click on the sheet picture, top left next to file and select "View Code". Paste in this code:

Private Sub Workbook_Activate()
Application.MacroOptions Macro:="MYMacro", ShortcutKey:="s"
End Sub

Private Sub Workbook_Deactivate()
Application.MacroOptions Macro:="MYMacro", ShortcutKey:=""
End Sub

This will assign the shortcut key Ctrl+S on activation and remove the shortcut key an deactivation.

Dave

OzGrid Business Applications