Problems with Application.OnKey in Add-In files

[alhypo]

Hello,

So basically I'm trying to create an add-in. Most of the functionality is in the ribbon but some procedures will just run better as keyboard shortcuts. I don't want the add-in to be a permanent fixture in Excel. In other words, the user will just open the add-in file directly (which causes a menu to be added to the ribbon) then exit the add-in when done. The add-in is closed using a button I put on its ribbon tab since you can't really close them directly. No problems there. All of that works fine.

My problem comes when I try to assign keyboard shortcuts. Here's what I have now:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Debug.Print "BF Close"
    Application.OnKey "+^A", ""
    
End Sub

Private Sub Workbook_Open()

    Debug.Print "Open"
    Application.OnKey "+^A", "TestSC"
    
End Sub

The "Open" part works just fine. However, when I exit the add-in (using the button in the ribbon), the shortcut assignment does not get removed. If I press CTRL-SHIFT-A after closing, it actually reopens the add-in and executes the procedure. It is especially strange that the "BeforeClose" procedure does definitely run because I see "BF Close" print in the immediate window when I exit.

Even more strange, if I type

Application.OnKey "+^A", ""

into the immediate window, it does manage to unassign the shortcut. So, as you can imagine, I'm pulling my hair out trying to figure out why this isn't working.

I've also tried this in the close event which is supposed to reset the shortcut to it's default but that doesn't work either:

Application.OnKey "+^A"

Thank you for your help.

 

[shg]

What are the first several lines of Sub TestSC() ?

 

[alhypo]

TestSC() is just there for testing purposes. I hate writing a bunch of code only to find I can't activate it the way I was thinking. This is all it is:

Public Sub TestSC()
    
    MsgBox prompt:="It worked", Buttons:=vbOKOnly
    
End Sub

TestSC() is located in a module while the events are in the ThisWorkbook module.

 

[shg]

I thought the shortcut may have been assigned on the module via an Attribute statement.

You're last code snippet is correct for removing a shortcut.

Instead of assigning the shortcut via code, you can assign it from the Macros dialog (which will cause the Attribute statement to appear if you exported the module containing the procedure). The assignment will go away automatically when the add-in is removed, and be restored when to install it again.

 

[alhypo]

OK, I will try that when I can get back to this and see if it works. If it does, great!

However, just to satisfy my own curiosity, does anyone know why the shortcut unassignment code in the close event is not working even though it does work in the immediate window?

Thanks!

 

[alhypo]

I thought the shortcut may have been assigned on the module via an Attribute statement.
...
Instead of assigning the shortcut via code, you can assign it from the Macros dialog (which will cause the Attribute statement to appear if you exported the module containing the procedure). The assignment will go away automatically when the add-in is removed, and be restored when to install it again.

This is an interesting side of VBA I was not really aware of. I'm not really finding much documentation about it either.

So I exported a module with a proceedure that had a shortcut assigned via the Macro dialog and get a bas file that contains something like this:

Public Sub delBlnkRows()
Attribute delBlnkRows.VB_ProcData.VB_Invoke_Func = "D\n14"

So that "Attribute" statement seems to be what determines the shortcut. It doesn't look like you can actually use these statements in the VBA window. They seem to be processed secretly by the VBA compiler.

Anyway...

The procedures in my Add-In do not show up in the Macro dialog. It wouldn't be a big deal to convert my add-in back to a regular Excel macro file so I could assign the shortcut, but to satisfy my curiosity, I tried the following instead. (It worked, by the way.)

1. In the VBA window, export the module that contains the procedure you want a shortcut for.

2. Open the .bsa file with a text editor.

3. Add the attribute line under the procedure declaration as follows:

Public Sub TestSC()
Attribute TestSC.VB_ProcData.VB_Invoke_Func = "A\n14"

Not sure exactly what the syntax for the shortcut key is but the above results in the shortcut CTRL-SHIFT-A. You can change the "A" to other letters to get the shortcut you want.

4. Save the .bsa file.

5. Back in VBA, delete the module you exported from your file.

6. Import the .bsa file you modified.

7. Save everything and exit Excel.

So now my shortcut is automatically assigned whenever the Add-In is open. The shortcut disappears when the Add-In is exited. Works great!

Thanks shg!

 

[shg]

Good job, glad you got it sorted.