Assigning keyboard shortcut to VBA macro programmatically

Question

Using OLE, I create an Excel sheet with graphs and some macros to manipulate them. I'd like to assign keyboard shotcuts to the macros, so that the user has easy access to them. I can't figure out - however - how to do that. Any suggestions? Thanks in advance.

Answer 1

Hi

 

Using the macro recorder in Excel often gives an answer. I assigned Ctr+Shift+F to macro1 and recorded this as a macro. results are below:

 

Application.MacroOptions Macro:="Macro1", Description:= _
        "Macro recorded 12/06/2007 by Tony Green", ShortcutKey:="F"

 

 

Answer 2

You can use Application.Onkey to set the key for the macro

 

Here you have the sample code and commonly used keys : http://vbadud.blogspot.com/2007/06/assigning-shortcut-keys-excel-macros.html

 

Cheers

Shasur

Answer 3

Thanks, that works!