Dynamically adding a handler for a keypress action

Posts: 36
Joined: 2014-12-17

Actually, after further testing, I'm proving myself wrong.

If I leave no handler for the KeyPress, it just kills Excels keyboard shortcut completely. That's not what I'm trying to do at all... :(

Ken Puls, FCPA, FCMA, MS MVP

Ken Puls

Posts: 36
Joined: 2014-12-17

Actually, after further testing, I'm proving myself wrong.

If I leave no handler for the KeyPress, it just kills Excels keyboard shortcut completely. That's not what I'm trying to do at all... :(

Ken Puls, FCPA, FCMA, MS MVP

Ken Puls

Posts: 36
Joined: 2014-12-17

I've tried another approach to this, but am still struggling. It feels like I'm very close though... Here's what I have, which is all contained in my "Globals" module:

    Public WithEvents g_keyCTRL_L As AddinExpress.MSO.ADXKeyboardShortcut
    Public WithEvents g_keyCTRL_T As AddinExpress.MSO.ADXKeyboardShortcut

    Public Sub HookKeyboardShortCut(sShortCutText As String)

        'Create the keyboard shortcut
        Select Case sShortCutText
            Case Is = "Ctrl+L"
                g_keyCTRL_L = New AddinExpress.MSO.ADXKeyboardShortcut()

                With g_keyCTRL_L
                    .ShortcutText = sShortCutText
                    .SupportedApps = AddinExpress.MSO.ADXOfficeHostApp.ohaExcel
                    AddHandler .Action, New AddinExpress.MSO.ADXAction_EventHandler(AddressOf keyCTRL_L_Action)
                End With

            Case Is = "Ctrl+T"
                g_keyCTRL_T = New AddinExpress.MSO.ADXKeyboardShortcut()

                With g_keyCTRL_T
                    .ShortcutText = sShortCutText
                    .SupportedApps = AddinExpress.MSO.ADXOfficeHostApp.ohaExcel
                    AddHandler .Action, New AddinExpress.MSO.ADXAction_EventHandler(AddressOf keyCTRL_T_Action)
                End With
            Case Else
                Exit Sub
        End Select

    End Sub

    Public Sub UnHookKeyboardShortcut(sShortCutText As String) 'Handles keyCTRL_L.Action
        Select Case sShortCutText
            Case Is = "Ctrl+L"
                RemoveHandler g_keyCTRL_L.Action, AddressOf keyCTRL_L_Action
                g_keyCTRL_L = Nothing
            Case Is = "Ctrl+T"
                RemoveHandler g_keyCTRL_T.Action, AddressOf keyCTRL_T_Action
                g_keyCTRL_T = Nothing
        End Select
    End Sub

    Private Sub keyCTRL_T_Action(sender As Object) 'Handles keyCTRL_T.Action
        'Override keyboard shortcut CTRL + T

        Try
            'Call the standard "Insert Table" functionality
            g_KeyPressed = "CTRL+T"
            xlApp.CommandBars.ExecuteMso("TableInsertExcel")
        Catch ex As Exception
            'Triggered because Insert Table functionality is disabled, so just ignore
            g_KeyPressed = vbNullString
        End Try

    End Sub
    Private Sub keyCTRL_L_Action(sender As Object) 'Handles keyCTRL_L.Action
        'Override keyboard shortcut CTRL + L

        Try
            'Call the standard "Insert Table" functionality
            g_KeyPressed = "CTRL+L"
            xlApp.CommandBars.ExecuteMso("TableInsertExcel")
        Catch ex As Exception
            'Triggered because Insert Table functionality is disabled, so just ignore
            g_KeyPressed = vbNullString
        End Try

    End Sub

In my addin module's AddinInitialize event, I now check if the shortcuts should be hooked, and call the subroutine with:

 HookKeyboardShortCut("Ctrl+L")

I've stepped through the code, and the HookKeyboardShortCut code does run. But when I then try to use the commands it still provides the default keyboard shortcut behaviour.

The good news is that I can get this to compile, and change the options dynamically. The bad news is that it is getting completely ignored. I'm feeling that I haven't hooked the event properly to the AddinModule somehow, but I'm not sure how to do it. I believe in the original code created by doing a button via the UI, there was a component inside the parenthesis:

New AddinExpress.MSO.ADXKeyboardShortcut(Me.Components)

Is this required? What would I put in there, as I can't seem to figure out how to tie this back to "me" (which I believe is the Addin module?)

Ken Puls, FCPA, FCMA, MS MVP

Andrei Smolin

Add-in Express team

Posts: 19122
Joined: 2006-05-11

Hello Kevin,

Ken Puls writes:
Is this required?

Yes. There's a lot of such calls in the InitializeComponent() method; see AddinModule.Designer.vb.

Ken Puls writes:
What would I put in there, as I can't seem to figure out how to tie this back to "me" (which I believe is the Addin module?)

If you are in the add-in module, Me returns the instance of the add-in module which is connected to Office.
If you are outside of the add-in module, you get that instance via {your project}.AddinModule.CurrentInstance.

Regards from Poland (GMT+2),

Andrei Smolin
Add-in Express Team Leader

Ken Puls

Posts: 36
Joined: 2014-12-17

Thanks Andrei,

Okay, so I've updated the code to add 'MonkeyTools.AddinModule.CurrentInstance' as follows:


    'Global variables in the 'Globals' module
    Public WithEvents g_keyCTRL_L As AddinExpress.MSO.ADXKeyboardShortcut
    Public WithEvents g_keyCTRL_T As AddinExpress.MSO.ADXKeyboardShortcut

    Public Sub HookKeyboardShortCut(sShortCutText As String)

        'Create the keyboard shortcut
        Select Case LCase(sShortCutText)
            Case Is = "ctrl+l"
                g_keyCTRL_L = New AddinExpress.MSO.ADXKeyboardShortcut(MonkeyTools.AddinModule.CurrentInstance)

                With g_keyCTRL_L
                    .ShortcutText = sShortCutText
                    .SupportedApps = AddinExpress.MSO.ADXOfficeHostApp.ohaExcel
                    AddHandler .Action, New AddinExpress.MSO.ADXAction_EventHandler(AddressOf keyCTRL_L_Action)
                End With

            Case Is = "ctrl+t"
                g_keyCTRL_T = New AddinExpress.MSO.ADXKeyboardShortcut(MonkeyTools.AddinModule.CurrentInstance)

                With g_keyCTRL_T
                    .ShortcutText = sShortCutText
                    .SupportedApps = AddinExpress.MSO.ADXOfficeHostApp.ohaExcel
                    AddHandler .Action, New AddinExpress.MSO.ADXAction_EventHandler(AddressOf keyCTRL_T_Action)
                End With
            Case Else
                Exit Sub
        End Select

    End Sub

I have stepped through a call to HookKeyboardShortcut("Ctrl+L") and am getting the following error when I execute the line that starts 'g_keyCTRL_L = New...":

Detailed technical information follows: 
---
Exception Source:      MonkeyTools
Exception Type:        System.InvalidCastException
Exception Message:     Unable to cast object of type 'MonkeyTools.AddinModule' to type 'System.ComponentModel.IContainer'.
Exception Target Site: HookKeyboardShortCut

---- Stack Trace ----
   MonkeyTools.modGlobalParameters.HookKeyboardShortCut(sShortCutText As String)
       Globals.vb: line 2312, col 17, IL 0048 (0x30)
   MonkeyTools.AddinModule.AddinModule_AddinInitialize(sender As Object, e As EventArgs)
       AddinModule.vb: line 2404, col 111, IL 0972 (0x3CC)
   AddinExpress.MSO.ADXAddinModule.AddinExpress.MSO.IDTExtensibility2.OnStartupComplete(custom As Array&)
       MonkeyTools.dll: N 0917 (0x395) IL

Any ideas?

Ken Puls, FCPA, FCMA, MS MVP

Ken Puls

Posts: 36
Joined: 2014-12-17

Got it.

I refactored it a bit so that I create the controls dynamically at startup, assign them to the global variables, and the just add/remove the handler on demand.

Global Variables


    Public WithEvents g_keyCTRL_L As AddinExpress.MSO.ADXKeyboardShortcut
    Public WithEvents g_keyCTRL_T As AddinExpress.MSO.ADXKeyboardShortcut

Initialized via AddinInitialize as follows:

        'Hook appropriate keyboard shortcut keys
        g_keyCTRL_L = New AddinExpress.MSO.ADXKeyboardShortcut(Me.Components)
        g_keyCTRL_T = New AddinExpress.MSO.ADXKeyboardShortcut(Me.Components)

        With g_keyCTRL_L
            .ShortcutText = "Ctrl+L"
            .SupportedApps = AddinExpress.MSO.ADXOfficeHostApp.ohaExcel
        End With
        With g_keyCTRL_T
            .ShortcutText = "Ctrl+T"
            .SupportedApps = AddinExpress.MSO.ADXOfficeHostApp.ohaExcel
        End With

And when called (either at startup via AddinInitiliaze or toggled via my Options form, these then run to add/remove the routines which stored in the Globals module (along with these two):

    Public Sub HookKeyboardShortCut(sShortCutText As String)
        Select Case LCase(sShortCutText)
            Case Is = "ctrl+l"
                AddHandler g_keyCTRL_L.Action, New AddinExpress.MSO.ADXAction_EventHandler(AddressOf keyCTRL_L_Action)
            Case Is = "ctrl+t"
                AddHandler g_keyCTRL_T.Action, New AddinExpress.MSO.ADXAction_EventHandler(AddressOf keyCTRL_T_Action)
            Case Else
                Exit Sub
        End Select
    End Sub
    Public Sub UnHookKeyboardShortcut(sShortCutText As String) 'Handles keyCTRL_L.Action
        Select Case LCase(sShortCutText)
            Case Is = "ctrl+l"
                RemoveHandler g_keyCTRL_L.Action, AddressOf keyCTRL_L_Action
            Case Is = "ctrl+t"
                RemoveHandler g_keyCTRL_T.Action, AddressOf keyCTRL_T_Action
        End Select
    End Sub

And now I'm asking why it took me so long to work this out as it looks pretty straight forward once it's all done. :(

Ken Puls, FCPA, FCMA, MS MVP

Andrei Smolin

Add-in Express team

Posts: 19122
Joined: 2006-05-11

Hello Ken,

An empiric rule, sort of: everything is simple if you know what to do; everything is very simple, if you did it at least twice. So again and again, we pay for the knowledge that makes us wiser. And better.

Regards from Poland (GMT+2),

Andrei Smolin
Add-in Express Team Leader