Shortcut key for add-in macro (Excel 2007)

[FHgm]

I cannot see how to see or change the hotkey assigned to a macro that is part
of an add-in. I can see/change on a "conventional" recorded macro.
(Developer>Macro>Options), but the Option button doesn't exist in the VB
Editor version. (VBE>Tools>Macros)

 

[Peter T]

There is no interface in the VBE for you to add the shortcut. Use the one in
Excel that you have already found or Alt-F8, Options.

You can edit the macro Description in the VBE via the Object browser, but
easier to use Alt-F8.

You can also add or edit the shortcut if you edit the bas module in a text
editor (after exporting it), but again easier to use Alt-F8 in Excel.

There is also Application.Onkey which you might like to look at in help.

Regards,
Peter T

 

[FHgm]

There is no interface in the VBE for you to add the shortcut. Use the one in
Excel that you have already found or Alt-F8, Options.

Thanks for the reply Peter,

I'm still having problems though - the macros that I have as an "add-in"
don't show up with Alt-F8. I'm not sure why. I think they did in 2003. I
added the short cut keys when I was using Excel 2003. But now that I'm using
2007, the macros in the "add-in" don't show up.

 

[Peter T]

Are you sure macros are enabled in your Excel 2007

Regards,
Peter T

 

[FHgm]

Are you sure macros are enabled in your Excel 2007

Regards,
Peter T

Yes, this is the odd thing. The macros work just fine. But there are NO
macros listed if I press Alt-F8 (in fact all the buttons except "Cancel" are
grey'ed out.)

 

[FHgm]

Easy to replicate:

Open new workbook (Cntl-N)
Press Record Macro
Name it, and assign to Cntl-Q
(have macro do something simple: add 2 numbers or something)
Now save worksheet as an add-in (either .xlam or .xla)
Close this workbook

Open new workbook
Go to options> addin : and now add in the just created .xla file

Note that Cntl-Q works - but there is no way to see this macro using Alt-F8
or to see what hotkey is assigned, or to reassign the key. (Or to run it if
you didn't know it existed...)

 

[Peter T]

I didn't realise you were talking about an addin. Macros in addins are not
visible in the Alt-F8 dialog in any Excel version. Normally their macros are
called from a menu button or a shortcut created with OnKey in the Open event
(as I mentioned last time, see OnKey in help).

If you really want to use the shortcut method you'd apply via Alt-F8, the
only way (other than temporarily changing the IsAddin property) is to edit
the bas module in a text method

Export the bas module and remove it
In say Notepad add the first two lines after the macro name


Sub MyMacro()
Attribute MyMacro.VB_Description = "my Macro description"
Attribute MyMacro.VB_ProcData.VB_Invoke_Func = "D\n14"

' shortcutkey is Ctrl-Shift-d
Msgbox My Macro

End Sub

Import the bas module

I should add this is not the recommended way to do it, use OnKey

Regards,
Peter T

 

[Peter T]

PS

Ensure the first line in the text editor is something like this (obviously
with the correct module name)

Attribute VB_Name = "Module1"

Peter T

 

[YossiD]

Ok, this reply is a few years late, but I just encountered the same problem and found a solution.

In the Macros dialog (Alt-F8), manually enter the macro name. Then you can click "Options" to assign a shortcut key. This works in Excel 2003 and 2007. I don't know about other versions.

I also came across the MacroOptions expression which apparently lets you assign the shortcut key from within the macro. The VBA help for this function isn't very good, but I found a decent example here:
msdn[dot]microsoft[dot]com/en-us/library/offic/aa202499%28v=office.10%29.aspx
(this is my 1st post so I can't use real links)

I have not tried this expression so I can't say if or how well it works.

Hope this info is still of some use to someone.