View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld[_2_] Ron Rosenfeld[_2_] is offline
external usenet poster
 
Posts: 1,045
Default How to list all user-defined keyboard shortcuts

On Thu, 10 Oct 2013 19:05:29 -0700 (PDT), Howard Silcock 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_Fun c = ""(\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
==============================