Setting Keyboard Shortcuts for locked XLA add in

[Harimau]

Hi all,

We use some external proprietary Excel tools for our work - it's added to
our Excel using an xla add-in. The tools have been added to our menu, and we
usually need to click on the tab, then choose a particular thing we want.
Similar to how we'd need to choose "Save" if we click on "File" at the top
menu.

Now, we use the tools almost constantly, but we hate having to go from the
keyboard to the mouse to activate the tool. We want to set a keyboard
shortcut for each the items in the mu, but the xla add-in itself is locked,
so we can't just modify its code like we would a normal add-in or macro.

Is there a way of achieving this?

We use Excel 2007 on Windows XP, if its relevant.

Thanks in advance!

Harimau

 

[Peter T]

Customize menus
right click the button that runs the procedure in the addin
Assign Macro...
note the Macro name

Now write your own little macro to assign a shortcut key, in this example
Ctrl #

Sub myShortCut()

Application.OnKey "^#", "'theAddin.xla'!macroName"

End Sub

Not the pair of apostrophes that embrace the addin name (normally not
necessary but just in case, followed by an ! and the macro name you noted
from customize menus.
See OnKey in help

You could put this in your Personal.xls, assign Ctrl-# to "myShortCut"
When you press Ctrl-# it will get re-assigned to the macro in the addin

Regards,
Peter T

 

[Harimau]

Thanks for the reply! However, that option doesn't seem to work for us.

When we right click on the menu item that triggers the add in, only the
following options come up:

"Add Group to Quick Access Toolbar" (greyed out)
"Customize Quick Access Toolbar"
"Show Quick Access Toolabr below the Ribbon"
"Minimise the Ribbon"

And yes, when we tried putting the menu item in the Quick Access toolbar
(using the Customize screen), it doesn't work.

 

[Peter T]

You did say in your OP you are using Excel 2007 but apologies I didn't
notice, I explained how to get the macro name from the menu button in
earlier versions.

I don't have '2007 but I assume there are equivalent methods first to find
the macro name then add a keyboard shortcut. Hopefully someone might chime
in.

Regards,
Peter T