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

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.

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.

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
==============================

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