Is it possible to list all macro keyboard shortcuts in a workbook?

[sadams1]

A spreadsheet I'm working with has many assigned macro keyboard shortcuts & no listing of what they are. When I assign a shortcut for a new macro, I'm getting "ALREADY ASSIGNED! TRY AGAIN!" However, these same assigned shortcuts throw errors. Is is possible to execute code that will list all of these so I know what is available?
Thanks!

 

[Tom Urtis]

One way, but take notice:
• Need trusted access to VBE (File > Options > Trust Center > Trust Center Settings > Macro Settings).
• Establish reference to Microsoft Visual Basic for Applications Extensibility 5.3.
• Modify for your desired destination sheet name or only execute the code when you know that the active worksheet if the worksheet where you want these macros listed.

Private Sub ListMacros()
Dim intLine%, intArgumentStart%, strLine$, objComponent As Object
For Each objComponent In ActiveWorkbook.VBProject.VBComponents
If objComponent.Type = 1 Then
For intLine = 1 To objComponent.CodeModule.CountOfLines
strLine = objComponent.CodeModule.Lines(intLine, 1)
strLine = Trim$(strLine) 'Remove indented spaces
If Left$(strLine, 3) = "Sub" Or Left$(strLine, 11) = "Private Sub" Then
intArgumentStart = InStr(strLine, "()")
If intArgumentStart > 0 Then
If Left$(strLine, 3) = "Sub" Then
MsgBox Mid$(strLine, 4, intArgumentStart - 4)
Else
MsgBox Mid$(strLine, 12, intArgumentStart - 12)
End If
End If
End If
Next intLine
End If
Next objComponent
End Sub

 

[sadams1]

Thank you very much for taking time to reply & I apologize for the delayed reply! Here's what I'm doing & the result...

1. as a test, I started a blank workbook
2. added a simple macro with a keyboard shortcut (screenshot 1)
3. added your code (screenshot 2)
4. ran "module 2" & get a dialog box showing the macro name (screenshot 3)

Am I doing something wrong that the keyboard shortcut for "macro 1" isn't being listed?

Thanks!

 

[Tom Urtis]

The macro I posted did what it was supposed to do which was to show the message box with the macro name.
I suggested that you modify the code to reference and list the macros on a separate worksheet depending on what sheet you wanted, which I would not know because I do not see your workbook.
If you are not sure how to do that, this is one way:

Sub ListMacros()
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With

Dim strSheetName$, strLine$
Dim intLine%, intArgumentStart%
Dim xRow&, objComponent As Object
strSheetName = "zzzCompiler"
xRow = 1

On Error Resume Next
Sheets("zzzCompiler").Delete
Err.Clear
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "zzzCompiler"

For Each objComponent In ActiveWorkbook.VBProject.VBComponents
If objComponent.Type = 1 Then
For intLine = 1 To objComponent.CodeModule.CountOfLines
strLine = objComponent.CodeModule.Lines(intLine, 1)
strLine = Trim$(strLine)

If Left$(strLine, 3) = "Sub" Or Left$(strLine, 11) = "Private Sub" Then
intArgumentStart = InStr(strLine, "()")
If intArgumentStart > 0 Then

If Left$(strLine, 3) = "Sub" Then
Cells(xRow, 1).Value = Trim(Mid$(strLine, 4, intArgumentStart - 4))
xRow = xRow + 1
ElseIf Left$(strLine, 10) = "Public Sub" Then
Cells(xRow, 1).Value = Trim(Mid$(strLine, 4, intArgumentStart - 11))
xRow = xRow + 1
Else
Cells(xRow, 1).Value = Trim(Mid$(strLine, 12, intArgumentStart - 12))
xRow = xRow + 1
End If

End If
End If

Next intLine
End If
Next objComponent

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

 

[sadams1]

sorry for not clarifying what I was looking for & also that my coding ability is limited at best. Thank you again for the additional guidance & again greatly appreciate your time & patience!

 

[sadams1]

I apologize for the additional post but would it be possible to list the keyboard shortcut, if any, associated with each macro?
If too much of pain, just tell me to buzz off.

 

[Tom Urtis]

Not telling you to buzz off, but I would respectfully suggest that you first attempt a search on your own, which if you had would have revealed this thread on this website that addresses what you are asking for from YasserKhalil's solution, or what would have at least pointed you in the right direction to modify that code for your situation. Note the references that need to be established.

How do I get a list of the hotkeys I've assigned to Excel VBA macros?

I'm using Excel 2016 on Windows 10. I've used Macro Options to assign keys: and know I can use "Application.OnKey" to make assignments, but despite many searches, I've been unable to find a way to get a list of what I've assigned using the Macro Options dialogue. I know could do a simple...

www.mrexcel.com

 

[sadams1]

Again, greatly appreciate your time & insight. I search & mess around with many examples before asking for help so the question are as specific as possible...in this case, had I thought of the word "hotkey" most likely would have Yasser's post. I posted a comment in that thread asking if "keyboard shortcut" could be added to the title or in the body so others who may be searching for something similar will find it.
Thank you again for your help!

 

[Tom Urtis]

No problem, all good, thanks for the follow-up.