Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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
==============================
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
comprehensive list of keyboard shortcuts? Johannes Zellner Excel Discussion (Misc queries) 2 August 2nd 06 01:51 PM
How do I create keyboard shortcuts for a validation list? Jo Excel Discussion (Misc queries) 0 April 5th 06 04:27 PM
Keyboard shortcuts Scott Excel Discussion (Misc queries) 2 January 25th 06 05:51 AM
KEYBOARD SHORTCUTS rm Excel Discussion (Misc queries) 8 May 18th 05 08:48 AM
Print a List of Keyboard Shortcuts in Excel 2003 Curt Frye Excel Programming 5 July 9th 04 12:30 AM


All times are GMT +1. The time now is 03:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"