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 we have an Application method to associated any key/key sequence to 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 Execl 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 to assigning other characters for macro shortcut keys.