This site will look better if you upgrade to a browser that supports web standards.

McGimpsey & Associates 

Macro keyboard shortcut bug workaround

In MacXL 98/01/v.X, if you define a keyboard shortcut for a macro, using the Tools/Macro/Macros dialog:

the resuting Command-Option-<letter> combination will override the Control-<letter> keyboard shortcut as well. For instance, Control-l (lowercase L) by default brings up the Define Name dialog, but if you assign Command-Option-l to a macro, that macro will fire instead. One workaround is to explicitly restore the Control-l shortcut when the file containing the macro is opened, using VBA's Application.OnKey command. This macro goes in the ThisWorkbook module of your workbook containing the macro keyboard shortcut:

    Private Sub Workbook_Open()
        Application.OnKey "^l", "ResetCtrlL"
    End Sub

Since this applies for the entire Application session, we also need to reset the shortcut when the workbook is closed:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Application.OnKey "^l"
    End Sub

This macro, placed in a regular code module, restores Control-l to open the Define Name dialog:

    Public Sub ResetCtrlL()
        CommandBars("Worksheet Menu Bar").Controls( _
           "Insert").Controls("Name").Controls("Define...").Execute
    End Sub    

If you have additional macros that you're giving keyboard shortcuts to, they'll each need a macro to reset the Control-shortcut, but you can put all the Application.OnKey statments into the same Workbook_Open() and Workbook_BeforeClose() macros.

Last Updated: Wednesday, 31 December 2003