How to list all user-defined keyboard shortcuts

[howard....@gmail.com]

Does anyone know how to use VBA to produce a list of all the keyboard shortcuts I've set up within Excel? I've done this for Word but the Excel object model isn't like the Word model in this respect and I can't see how to do it.

Howard

[Ron Rosenfeld]

On Thu, 10 Oct 2013 16:28:59 -0700 (PDT), howard....@gmail.com wrote:

>Does anyone know how to use VBA to produce a list of all the keyboard shortcuts I've set up within Excel? I've done this for Word but the Excel object model isn't like the Word model in this respect and I can't see how to do it.
>
>Howard

I haven't done it but I know the general algorithm for shortcut keys you have assigned to macros.

In the "Trust Center" you need to Trust Access to the VBA Project Object Model. See Chip Pearson's web site on programming the VBA Editor

Loop through all the modules
Select those which are Code Modules
Export those modules to a text file

Those text files will have the shortcut key listed (as well as the code and the procedure name), so you can parse it out and save it someplace.

[Howard Silcock]

Gee, thanks for this. It does seem a bit of a case of using a sledgehammer to crack a nut, but it's an interesting approach.

[Ron Rosenfeld]

On Thu, 10 Oct 2013 19:05:29 -0700 (PDT), Howard Silcock <howard....@gmail.com> wrote:


>> Those text files will have the shortcut key listed (as well as the code and the procedure name), so you can parse it out and save it someplace.
>
>Gee, thanks for this. It does seem a bit of a case of using a sledgehammer to crack a nut, but it's an interesting approach.

Here is a VBA routine that will list those macros that have shortcuts that were assigned from the macro dialog box Options:
It lists the results in the Immediate window, but you could modify it to place the results on a worksheet.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

Be sure to follow the notes in the macro regarding setting reference and also be sure to set the option, in Excel Options, to trust access to the VBA project

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.
===================================================
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 ListMacroShortCutKeys()
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
==============================

[esybe...@gmail.com]

This macro appears to run for me but nothing shows up in the Immediate window. I can even step through it and it will cycle through the VBComp routine 5 times for the 5 macro shortcuts I have assigned thus far, but still nothing in the Immediate window. Any ideas? Thanks in advance.
Ed

[amcnam...@gmail.com]

this appears to no longer be working in excel 2016