Excel VBA to display key bindings (OnKey?)

Question

I work with a large set of Excel vba scripts, and some of them are related to Ctrl key combinations.

I know how to install one at a time in the user interface for Excel: pull out the list of vba scripts (Alt-F8), select the script and click "Options". Then you can bind / untie the script with the Ctrl key combination. However, you can bind more than one script to the same key, and Excel will select one (perhaps the first one that it will find in some way) and ignore the other bindings.

So, I want to assign Ctrl-e to the script, but first I have to find which other script (s) that it is currently associated with from a list of hundreds of scripts.

Is there a way to get Excel to list the current key bindings (with a VBA macro, I suppose)? I saw a solution for Word that looks at the KeyBindings collection. However, this does not work in Excel. Does Excel have another object?

Answer 1

After some time searching, I could not find any way to programmatically get a list of all the key bindings.

However, if you basically want to know what procedure is performed on the shortcut, but you are not sure and do not want to scan through your personal book, add-ons, etc., you can create a dynamic one that will always stop in the first line of VBA code. To do this, simply use the Add Watch dialog (right-click in the code window), enter the following parameters:

Then just follow the shortcut that interests you, and VBE will show you the procedure attached to it ...

Answer 2

You can list the keys assigned to a macro with a VBA macro. It turns out that although the shortcut key is not available "directly", if you export the module, any hotkey is specified in the exported file. This file can then be parsed to return the name of the procedure and the corresponding shortcut.

The line in the exported file with this information is as follows:

MacroShortCutKeys .VB_ProcData.VB_Invoke_Func = " a \ n14" Attribute

The bold information above is the name of the macro and its associated key combination. I don't know the value of \ n14 at the end (and I'm not sure if it will be consistent since I haven't tested it extensively.

Running the macro below requires that you set the option Trust access to the VBA Project object model in the settings of the trust center; and also that you are setting links in VBA as indicated in the code.

I just displayed the results in the Immediate window, but you can also easily place it on a sheet.

The exported file is stored in the C: \ Temp folder, and the file is deleted when we are done with it. If C: \ Temp does not exist, you will need to create it. (This could be done in a macro, but I was lazy).

If the macro does not have a shortcut, it will not be specified.

EDIT The routine lists only those shortcuts that were assigned using the Macro dialog box in an Excel worksheet. It does NOT display keyboard shortcuts that have been assigned using the Application.OnKey method. Not sure how to get them.

 Option Explicit 'MUST set to Trust Access to the VBA Project Object Model ' in Excel Options 'Set reference to: 'Microsoft Visual Basic for Applications Extensibility 'Microsoft Scripting Runtime 'Microsoft VBScript Regular Expressions 5.5 Sub MacroShortCutKeys() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As CodeModule Dim LineNum As Long Dim ProcKind As VBIDE.vbext_ProcKind Dim sProcName As String, sShortCutKey As String Const FN As String = "C:\Temp\Temp.txt" Dim S As String Dim FSO As FileSystemObject Dim TS As TextStream Dim RE As RegExp, MC As MatchCollection, M As Match Set RE = New RegExp With RE .Global = True .IgnoreCase = True .Pattern = "Attribute\s+(\w+)\.VB_ProcData\.VB_Invoke_Func = ""(\S+)(?=\\)" End With Set FSO = New FileSystemObject Set VBProj = ActiveWorkbook.VBProject For Each VBComp In VBProj.VBComponents Select Case VBComp.Type Case Is = vbext_ct_StdModule VBComp.Export FN Set TS = FSO.OpenTextFile(FN, ForReading, Format:=TristateFalse) S = TS.ReadAll TS.Close FSO.DeleteFile (FN) If RE.Test(S) = True Then Set MC = RE.Execute(S) For Each M In MC Debug.Print VBComp.Name, M.SubMatches(0), M.SubMatches(1) Next M End If End Select Next VBComp End Sub