CODEWITHSUNDEEP
excelvbaexcel-addins
Nagasai Tenekondala
Nagasai Tenekondala

Reputation: 58

Assign a keyboard shortcut to a Microsoft Excel Add-in

My friend shared a .bas file with me and told me to save it as .xlam in the vbEditor to have it as an Addin.

I've browsed in Add-ins and am able to enable it in my workbook.

Is there a way I can assign a keyboard shortcut to the Add-in? There's only one sub in that add-in file now.

I tried writing another sub with 

Application.onKey "+^{C}" ,'Calculate'

But it doesn't trigger the sub to be executed.

Upvotes: 2

Views: 4054

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71167

You don't assign a shortcut to an add-in. Rather, you assign a shortcut to a macro - that is, a Public Sub procedure in your standard module.

So your code file might look like this:

Option Explicit

Public Sub Calculate()
    '...code...
End Sub

Open it in Notepad. I'll look like this:

Attribute VB_Name = "Module1"
Option Explicit

Public Sub Calculate()
    '...code...
End Sub

Under Public Sub Calculate(), you want to add an attribute so that the file looks like this:

Attribute VB_Name = "Module1"
Option Explicit

Public Sub Calculate()
Attribute Calculate.VB_ProcData.VB_Invoke_Func = "C\n14"
    '...code...
End Sub

This is exactly how Excel's macro recorder assigns macro hotkeys: no need for any Application.OnKey work-arounds.

Save the file, import it into your VBA project: Ctrl+Shift+C will now invoke that macro.

If you're using Rubberduck, forget all of the above and just go to your module in the VBA editor, find the procedure and annotate it like so:

Option Explicit

'@ExcelHotkey("C")
Public Sub Calculate()
    '...code...
End Sub

Where "C" will make the hotkey Ctrl+Shift+C; I'd warmly recommend not using "c" to avoid hijacking Ctrl+C (Copy).

Bring up the code inspections toolwindow, hit the "refresh" button; under "Rubberduck Opportunities" there should be an inspection result warning about annotations & attributes being out of sync - select "add member attribute" from the "Fix" menu, and you're done - no need to export/edit/import or deal with any obscure syntax, and if you want to change the hotkey, simply edit the comment accordingly and re-synchronize annotations & attributes.

See VB_Attribute annotations for more information.

As for saving your VBA project as an add-in, simply save your VBA project's host workbook as a .xlam add-in file, then close Excel altogether and re-open it - load your .xlam from the Developer Ribbon tab's "Excel Add-ins" button (hit "Browse" to locate your .xlam file if it's not in the list).

Upvotes: 3

Related Questions