Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to list all user-defined keyboard shortcuts
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to list all user-defined keyboard shortcuts
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ============================== |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to list all user-defined keyboard shortcuts
On Friday, October 11, 2013 at 7:49:54 PM UTC-7, Ron Rosenfeld wrote:
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 ============================== On Friday, October 11, 2013 at 7:49:54 PM UTC-7, Ron Rosenfeld wrote: 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 ============================== 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to list all user-defined keyboard shortcuts
On Saturday, December 16, 2017 at 5:47:19 PM UTC-6, wrote:
On Friday, October 11, 2013 at 7:49:54 PM UTC-7, Ron Rosenfeld wrote: 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 ============================== On Friday, October 11, 2013 at 7:49:54 PM UTC-7, Ron Rosenfeld wrote: 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 ============================== 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 this appears to no longer be working in excel 2016 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comprehensive list of keyboard shortcuts? | Excel Discussion (Misc queries) | |||
How do I create keyboard shortcuts for a validation list? | Excel Discussion (Misc queries) | |||
Keyboard shortcuts | Excel Discussion (Misc queries) | |||
KEYBOARD SHORTCUTS | Excel Discussion (Misc queries) | |||
Print a List of Keyboard Shortcuts in Excel 2003 | Excel Programming |