Recently I had an Excel VBA project where the client wanted to use CTRL-ALT-9 as the shortcut key to run a macro. For most of us when we create macros we assign a shortcut key sequence by going into Developer > Macros > Options… and assigning the shortcut key. The problem with this method is that it only allows lower and upper case letters to be used with the CTRL key.
Fortunately there is an Application method to associate any key/key sequence to a procedure. We use the Application.OnKey method to assign the key/key sequence to press to run a specified procedure. It just requires running code when the workbook opens and closes.
To assign the shortcut key, VBA code is added to ThisWorkbook in the Microsoft Excel Objects. When the workbook is opened we assign the shortcut key/key sequence. When the workbook is closed we return the key/key sequence to its normal meaning. Sample code is provided below.
' run when workbook is opened
Private Sub Workbook_Open()
' sets the hot key to CTRL-ATL-9 to run ConvertTableMacro
Application.OnKey "^%9", "ConvertTableMacro"
End Sub
' run when workbook is closing
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' resets the CTRL-ATL-9 hot key to normal meaning
Application.OnKey "^%9"
End Sub
The Microsoft link above provides all the information related to special characters for key sequences. For example “^” is the CTRL key, “%” is the ALT key, and “+” is the Shift key. This is a very simple solution for assigning other characters as macro shortcut keys.
