Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
identifying shortcut keys associated with custom macros
In Excel95 or Excel97 is there a way to obtain a list of all the
keyboard shortcuts that have been assigned to custom macros? I have an Excel based application that has over a 100 macros, many of which are associated with keyboard shortcuts. Unfortunately, as this tool has been developed over the years, I have not kept track of which keys were assigned to which macros. I note that when I hit a certain key board combination by accident, the program executes a macro. I want to find out which macro is being executed without having to investigate each of the macros individually. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
identifying shortcut keys associated with custom macros
I don't believe it is possible to get a list of custom keyboard
shortcuts. "windsurferLA" wrote in message ... In Excel95 or Excel97 is there a way to obtain a list of all the keyboard shortcuts that have been assigned to custom macros? I have an Excel based application that has over a 100 macros, many of which are associated with keyboard shortcuts. Unfortunately, as this tool has been developed over the years, I have not kept track of which keys were assigned to which macros. I note that when I hit a certain key board combination by accident, the program executes a macro. I want to find out which macro is being executed without having to investigate each of the macros individually. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
identifying shortcut keys associated with custom macros
I don't believe there is.
-- Regards, Tom Ogilvy "windsurferLA" wrote in message ... In Excel95 or Excel97 is there a way to obtain a list of all the keyboard shortcuts that have been assigned to custom macros? I have an Excel based application that has over a 100 macros, many of which are associated with keyboard shortcuts. Unfortunately, as this tool has been developed over the years, I have not kept track of which keys were assigned to which macros. I note that when I hit a certain key board combination by accident, the program executes a macro. I want to find out which macro is being executed without having to investigate each of the macros individually. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
identifying shortcut keys associated with custom macros
Tim Childs posted a routine that seemed to work for him in xl2002 (but he said
that it didn't in xl2k--no info on xl97): http://groups.google.com/groups?&thr...%40tkmsftngp07 (maybe you could find someone with xl2002 and have them try it for you.) windsurferLA wrote: In Excel95 or Excel97 is there a way to obtain a list of all the keyboard shortcuts that have been assigned to custom macros? I have an Excel based application that has over a 100 macros, many of which are associated with keyboard shortcuts. Unfortunately, as this tool has been developed over the years, I have not kept track of which keys were assigned to which macros. I note that when I hit a certain key board combination by accident, the program executes a macro. I want to find out which macro is being executed without having to investigate each of the macros individually. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
identifying shortcut keys associated with custom macros
The following code will return what you are after:
Sub ListShortCutMenus() Row =1 For Each cbar In CommandBars If cbar.Type = msoBarTypePopup then Cells(Row, 1) = cbar.Index Cells(Row, 2) = cbar.Name For col = 1 To cbar.Controls.Count Cells(Row, col +2) = _ cbar.Controls(col).Caption Next Col Row = Row + 1 End If Next cbar End Su -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
identifying shortcut keys associated with custom macros
He said short cut KEYS, not short cut Menus.
-- Regards, Tom Ogilvy jpendegraft wrote in message ... The following code will return what you are after: Sub ListShortCutMenus() Row =1 For Each cbar In CommandBars If cbar.Type = msoBarTypePopup then Cells(Row, 1) = cbar.Index Cells(Row, 2) = cbar.Name For col = 1 To cbar.Controls.Count Cells(Row, col +2) = _ cbar.Controls(col).Caption Next Col Row = Row + 1 End If Next cbar End Sub --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
identifying shortcut keys associated with custom macros
This worked for me on Xl97 and Xl2000 .... sory don't have Xl95
Option Explicit Const strAttrShC As String = "VB_ProcData.VB_Invoke_Func = " Const strAttrSub As String = "Attribute " Const strFoobar As String = "ZZZZzzzz" Dim strShortCuts() As String Dim j As Integer Sub mGetShortCutKeys() '// By Ivan F Moala '// http://www.XcelFiles.com '// Testing done Xl97 & 2000 '// Needs a Reference to MS Visual Basics for Applications Extensibilty lib Dim strTempModFile As String Dim NoComponents As Long Dim i As Integer Dim VBP As Object Set VBP = ActiveWorkbook.VBProject NoComponents = VBP.VBComponents.Count '// Set a Temp path strTempModFile = ActiveWorkbook.Path & Application.PathSeparator & "Tmp.Txt" '// inialize count j = 0 On Error Resume Next For i = 1 To NoComponents '// We only want Modules If VBP.VBComponents(i).Type = 1 Then With VBP.VBComponents(i) '// Export The ActiveWorkbooks CodeModule .Export strTempModFile ReadAttribute strTempModFile End With End If Next '// Now display it to a Sheet With ActiveWorkbook .Sheets.Add .ActiveSheet.[A1].Resize(UBound(strShortCuts()) + 1, 1) = _ Application.WorksheetFunction.Transpose(strShortCu ts()) .ActiveSheet.Columns("A").Columns.AutoFit .ActiveSheet.Columns("A").Columns.HorizontalAlignm ent = xlLeft End With Erase strShortCuts() End Sub Function ReadAttribute(strBas As String) As String Dim strTxt As String Dim handle As Long Dim Pos As Long Dim NewPos As Long Dim PosSub As String Dim x As Integer Dim ShortCutKey As String Dim SubName As String Dim blnShift As Boolean '// Open bas file in binary mode handle = FreeFile Open strBas For Binary As #handle '// Parse enougth spaces for text strTxt = Space(LOF(handle)) '// Read the string IN and Close the file Get #handle, , strTxt Close #handle '// Lets get the ShortCut Key! Pos = 0: NewPos = 0: x = 0 Do Pos = InStr(NewPos + 1, strTxt, strAttrShC) ShortCutKey = Mid(strTxt, Pos + Len(strAttrShC) + 1, 1) '// Is it a shortCut If ShortCutKey = " " Then GoTo Skip If Pos Then '// Build SC Key blnShift = (Asc(ShortCutKey) < 97) ShortCutKey = IIf(blnShift, "Ctrl + shift + " & ShortCutKey, "Ctrl + " & ShortCutKey) x = Pos Do Until PosSub = " " PosSub = Mid(strTxt, x - 1, 1) x = x - 1 Loop SubName = Mid(strTxt, x, Pos - x - 1) ReDim Preserve strShortCuts(j) strShortCuts(j) = "Sub Routine Name:= " & SubName & _ " [ ShortCut:= " & ShortCutKey & " ]" j = j + 1 PosSub = strFoobar End If Skip: NewPos = Pos Loop Until Pos = 0 '// Cleanup - Delete it Kill strBas End Function windsurferLA wrote in message ... In Excel95 or Excel97 is there a way to obtain a list of all the keyboard shortcuts that have been assigned to custom macros? I have an Excel based application that has over a 100 macros, many of which are associated with keyboard shortcuts. Unfortunately, as this tool has been developed over the years, I have not kept track of which keys were assigned to which macros. I note that when I hit a certain key board combination by accident, the program executes a macro. I want to find out which macro is being executed without having to investigate each of the macros individually. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
identifying shortcut keys associated with custom macros
windsurferLA wrote in message ...
In Excel95 or Excel97 is there a way to obtain a list of all the keyboard shortcuts that have been assigned to custom macros? I have an Excel based application that has over a 100 macros, many of which are associated with keyboard shortcuts. Unfortunately, as this tool has been developed over the years, I have not kept track of which keys were assigned to which macros. I note that when I hit a certain key board combination by accident, the program executes a macro. I want to find out which macro is being executed without having to investigate each of the macros individually. Hi, Just a couple of ideas. I do not know if they apply to Excel 95/97. If you 'export' the modules that contain the macros you sould get text documents like: ================================================= Attribute VB_Name = "Module1" Sub Macro1() Attribute Macro1.VB_Description = "Macro recorded 04/02/2004 by Stratos" Attribute Macro1.VB_ProcData.VB_Invoke_Func = "g\n14" ' Keyboard Shortcut: Ctrl+g ActiveCell.FormulaR1C1 = "My name is Stratos." End Sub Sub Macro2() Attribute Macro2.VB_Description = "Macro recorded 04/02/2004 by Stratos" Attribute Macro2.VB_ProcData.VB_Invoke_Func = "D\n14" ' ' Keyboard Shortcut: Ctrl+Shift+D ' ActiveCell.FormulaR1C1 = "I do not remenber much." End Sub ================================================== I am not sure what you can do with that, but you could possibly: - find (if exists) a VBE object that allow access to the Macro1.VB_ProcData.VB_Invoke_Func attribute (Excel VBE should maintain it somewhere in its object model) - loop through the module (macro by macro - VBE API allows it, if I remember correctly) and parse the text in it. - parse the exported text file and extract the 'Keyboard Shortcut' value or the 'VB_ProcData.VB_Invoke_Func attribute' value - do it manually Similarly you could loop through all code (using the VBE API) and automatically write code in each macro to 'print' somewhere (msgbox, statusbar) etc. the name of the executed macro. I hope this helps. If I have misunderstood your query please ignore this post. Best wishes, Stratos |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros will not run with shortcut keys | Excel Worksheet Functions | |||
macros shortcut keys don't work | New Users to Excel | |||
Macros Shortcut keys | Setting up and Configuration of Excel | |||
Changing shortcut keys on macros | Excel Worksheet Functions | |||
listing macros associated with shortcut keys | Excel Programming |