| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to "clear" all previous ShortcutKey Assignments
2003/2007
Have a series of Macros to assign different "sets" of ShortcutKey assignments like: Sub DT_ApplyShortCuts() With Application .MacroOptions macro:="DT_PswdAdd", Description:="Passwords Add", ShortcutKey:="A" .MacroOptions macro:="ClearAudit", Description:="Clear Audit Highlights", ShortcutKey:="C" .MacroOptions macro:="FM_FilterDiffReport", Description:="FM Filter the Difference Report", ShortcutKey:="d" .MacroOptions macro:="ExceptionCells", Description:="Exception Cells Highlighted", ShortcutKey:="E" .MacroOptions macro:="FindFormulaCells", Description:="Find and Highlight Formula Cells", ShortcutKey:="F" .MacroOptions macro:="ColumnCopy", Description:="Copy/Insert/ Move Constants Input column", ShortcutKey:="h" .MacroOptions macro:="FM_FilterDirList", Description:="FM Filter the Directory List", ShortcutKey:="k" .MacroOptions macro:="FM_CreateDirList", Description:="FM Create Directory List", ShortcutKey:="i" .MacroOptions macro:="DT_CopyLegend", Description:="FM Copy Legend", ShortcutKey:="l" .MacroOptions macro:="FM_LinkSheetInfo", Description:="FM Link Sheet Info", ShortcutKey:="L" .MacroOptions macro:="MapCells", Description:="Map Cells", ShortcutKey:="M" .MacroOptions macro:="ScreensClose", Description:="Close Screens", ShortcutKey:="n" .MacroOptions macro:="PswdRemove", Description:="Password Remove", ShortcutKey:="O" .MacroOptions macro:="DT_FormatSheets", Description:="DT Format Sheets", ShortcutKey:="P" .MacroOptions macro:="DT_PswdRemove", Description:="DT Passwords Remove", ShortcutKey:="Q" .MacroOptions macro:="ReplaceConstants", Description:="Replace Constants in Formulas", ShortcutKey:="R" .MacroOptions macro:="RemoveBorders", Description:="Remove Red Cell Borders", ShortcutKey:="r" .MacroOptions macro:="Foot_CrossFoot_Fix", Description:="Foot and CrossFoot Fix", ShortcutKey:="T" .MacroOptions macro:="ExtractConstantsInFormulas", Description:="Extract Constants in Formulas", ShortcutKey:="t" .MacroOptions macro:="PasswordsAllInternal", Description:="Clear all Passwords", ShortcutKey:="w" .MacroOptions macro:="ConsolCells", Description:="Consolidate Cells", ShortcutKey:="W" .MacroOptions macro:="Unhide_All", Description:="Unhide All", ShortcutKey:="X" .MacroOptions macro:="Screen", Description:="Add Second Screen", ShortcutKey:="y" .MacroOptions macro:="UsedRangeReset", Description:="Used Range Reset", ShortcutKey:="Z" End With End Sub In the ShortCutKey assignment above "k" is assigned to "FM_FilterDirList" with: .MacroOptions macro:="FM_FilterDirList", Description:="FM Filter the Directory List", ShortcutKey:="k" Via another Macro, I attempted to re-assign "k" to "XLPolySAP" but the assignment stayed FM_FilterDirList?? BTW, both macros are in the same module but named (and run) separately. What is happening, how can I avoid the issue in the future? TIA Dennis |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to "clear" all previous ShortcutKey Assignments
Dennis,
Try using the Onkey Method of the application object instead. Search for "OnKey" in the VBA Help. For example, to assign CTRL+k to XLPolySAP: application.OnKey "^k", "XLPolySAP" To clear the key assignment, you would use: application.OnKey "^k" -- Hope that helps. Vergel Adriano "Dennis" wrote: 2003/2007 Have a series of Macros to assign different "sets" of ShortcutKey assignments like: Sub DT_ApplyShortCuts() With Application .MacroOptions macro:="DT_PswdAdd", Description:="Passwords Add", ShortcutKey:="A" .MacroOptions macro:="ClearAudit", Description:="Clear Audit Highlights", ShortcutKey:="C" .MacroOptions macro:="FM_FilterDiffReport", Description:="FM Filter the Difference Report", ShortcutKey:="d" .MacroOptions macro:="ExceptionCells", Description:="Exception Cells Highlighted", ShortcutKey:="E" .MacroOptions macro:="FindFormulaCells", Description:="Find and Highlight Formula Cells", ShortcutKey:="F" .MacroOptions macro:="ColumnCopy", Description:="Copy/Insert/ Move Constants Input column", ShortcutKey:="h" .MacroOptions macro:="FM_FilterDirList", Description:="FM Filter the Directory List", ShortcutKey:="k" .MacroOptions macro:="FM_CreateDirList", Description:="FM Create Directory List", ShortcutKey:="i" .MacroOptions macro:="DT_CopyLegend", Description:="FM Copy Legend", ShortcutKey:="l" .MacroOptions macro:="FM_LinkSheetInfo", Description:="FM Link Sheet Info", ShortcutKey:="L" .MacroOptions macro:="MapCells", Description:="Map Cells", ShortcutKey:="M" .MacroOptions macro:="ScreensClose", Description:="Close Screens", ShortcutKey:="n" .MacroOptions macro:="PswdRemove", Description:="Password Remove", ShortcutKey:="O" .MacroOptions macro:="DT_FormatSheets", Description:="DT Format Sheets", ShortcutKey:="P" .MacroOptions macro:="DT_PswdRemove", Description:="DT Passwords Remove", ShortcutKey:="Q" .MacroOptions macro:="ReplaceConstants", Description:="Replace Constants in Formulas", ShortcutKey:="R" .MacroOptions macro:="RemoveBorders", Description:="Remove Red Cell Borders", ShortcutKey:="r" .MacroOptions macro:="Foot_CrossFoot_Fix", Description:="Foot and CrossFoot Fix", ShortcutKey:="T" .MacroOptions macro:="ExtractConstantsInFormulas", Description:="Extract Constants in Formulas", ShortcutKey:="t" .MacroOptions macro:="PasswordsAllInternal", Description:="Clear all Passwords", ShortcutKey:="w" .MacroOptions macro:="ConsolCells", Description:="Consolidate Cells", ShortcutKey:="W" .MacroOptions macro:="Unhide_All", Description:="Unhide All", ShortcutKey:="X" .MacroOptions macro:="Screen", Description:="Add Second Screen", ShortcutKey:="y" .MacroOptions macro:="UsedRangeReset", Description:="Used Range Reset", ShortcutKey:="Z" End With End Sub In the ShortCutKey assignment above "k" is assigned to "FM_FilterDirList" with: .MacroOptions macro:="FM_FilterDirList", Description:="FM Filter the Directory List", ShortcutKey:="k" Via another Macro, I attempted to re-assign "k" to "XLPolySAP" but the assignment stayed FM_FilterDirList?? BTW, both macros are in the same module but named (and run) separately. What is happening, how can I avoid the issue in the future? TIA Dennis |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to "clear" all previous ShortcutKey Assignments
I forgot to mention that if you do it using the MacroOptions, you need to
keep track of which macro is assigned to which key because you'll need to unassign it first before assigning it to a different macro.. for example: With Application 'remove shortcut for FM_FilterDirList .MacroOptions macro:="FM_FilterDirList", ShortcutKey:="" 'assign CTRL+k to XLPolySAP .MacroOptions macro:="XLPolySAP", ShortcutKey:="k" End With -- Hope that helps. Vergel Adriano "Vergel Adriano" wrote: Dennis, Try using the Onkey Method of the application object instead. Search for "OnKey" in the VBA Help. For example, to assign CTRL+k to XLPolySAP: application.OnKey "^k", "XLPolySAP" To clear the key assignment, you would use: application.OnKey "^k" -- Hope that helps. Vergel Adriano "Dennis" wrote: 2003/2007 Have a series of Macros to assign different "sets" of ShortcutKey assignments like: Sub DT_ApplyShortCuts() With Application .MacroOptions macro:="DT_PswdAdd", Description:="Passwords Add", ShortcutKey:="A" .MacroOptions macro:="ClearAudit", Description:="Clear Audit Highlights", ShortcutKey:="C" .MacroOptions macro:="FM_FilterDiffReport", Description:="FM Filter the Difference Report", ShortcutKey:="d" .MacroOptions macro:="ExceptionCells", Description:="Exception Cells Highlighted", ShortcutKey:="E" .MacroOptions macro:="FindFormulaCells", Description:="Find and Highlight Formula Cells", ShortcutKey:="F" .MacroOptions macro:="ColumnCopy", Description:="Copy/Insert/ Move Constants Input column", ShortcutKey:="h" .MacroOptions macro:="FM_FilterDirList", Description:="FM Filter the Directory List", ShortcutKey:="k" .MacroOptions macro:="FM_CreateDirList", Description:="FM Create Directory List", ShortcutKey:="i" .MacroOptions macro:="DT_CopyLegend", Description:="FM Copy Legend", ShortcutKey:="l" .MacroOptions macro:="FM_LinkSheetInfo", Description:="FM Link Sheet Info", ShortcutKey:="L" .MacroOptions macro:="MapCells", Description:="Map Cells", ShortcutKey:="M" .MacroOptions macro:="ScreensClose", Description:="Close Screens", ShortcutKey:="n" .MacroOptions macro:="PswdRemove", Description:="Password Remove", ShortcutKey:="O" .MacroOptions macro:="DT_FormatSheets", Description:="DT Format Sheets", ShortcutKey:="P" .MacroOptions macro:="DT_PswdRemove", Description:="DT Passwords Remove", ShortcutKey:="Q" .MacroOptions macro:="ReplaceConstants", Description:="Replace Constants in Formulas", ShortcutKey:="R" .MacroOptions macro:="RemoveBorders", Description:="Remove Red Cell Borders", ShortcutKey:="r" .MacroOptions macro:="Foot_CrossFoot_Fix", Description:="Foot and CrossFoot Fix", ShortcutKey:="T" .MacroOptions macro:="ExtractConstantsInFormulas", Description:="Extract Constants in Formulas", ShortcutKey:="t" .MacroOptions macro:="PasswordsAllInternal", Description:="Clear all Passwords", ShortcutKey:="w" .MacroOptions macro:="ConsolCells", Description:="Consolidate Cells", ShortcutKey:="W" .MacroOptions macro:="Unhide_All", Description:="Unhide All", ShortcutKey:="X" .MacroOptions macro:="Screen", Description:="Add Second Screen", ShortcutKey:="y" .MacroOptions macro:="UsedRangeReset", Description:="Used Range Reset", ShortcutKey:="Z" End With End Sub In the ShortCutKey assignment above "k" is assigned to "FM_FilterDirList" with: .MacroOptions macro:="FM_FilterDirList", Description:="FM Filter the Directory List", ShortcutKey:="k" Via another Macro, I attempted to re-assign "k" to "XLPolySAP" but the assignment stayed FM_FilterDirList?? BTW, both macros are in the same module but named (and run) separately. What is happening, how can I avoid the issue in the future? TIA Dennis |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to "clear" all previous ShortcutKey Assignments
Bingo! That explains it, Thanks
On Jul 25, 12:32 pm, Vergel Adriano wrote: I forgot to mention that if you do it using the MacroOptions, you need to keep track of which macro is assigned to which key because you'll need to unassign it first before assigning it to a different macro.. for example: With Application 'remove shortcut for FM_FilterDirList .MacroOptions macro:="FM_FilterDirList", ShortcutKey:="" 'assign CTRL+k to XLPolySAP .MacroOptions macro:="XLPolySAP", ShortcutKey:="k" End With -- Hope that helps. Vergel Adriano "Vergel Adriano" wrote: Dennis, Try using the Onkey Method of the application object instead. Search for "OnKey" in the VBA Help. For example, to assign CTRL+k to XLPolySAP: application.OnKey "^k", "XLPolySAP" To clear the key assignment, you would use: application.OnKey "^k" -- Hope that helps. Vergel Adriano "Dennis" wrote: 2003/2007 Have a series of Macros to assign different "sets" of ShortcutKey assignments like: Sub DT_ApplyShortCuts() With Application .MacroOptions macro:="DT_PswdAdd", Description:="Passwords Add", ShortcutKey:="A" .MacroOptions macro:="ClearAudit", Description:="Clear Audit Highlights", ShortcutKey:="C" .MacroOptions macro:="FM_FilterDiffReport", Description:="FM Filter the Difference Report", ShortcutKey:="d" .MacroOptions macro:="ExceptionCells", Description:="Exception Cells Highlighted", ShortcutKey:="E" .MacroOptions macro:="FindFormulaCells", Description:="Find and Highlight Formula Cells", ShortcutKey:="F" .MacroOptions macro:="ColumnCopy", Description:="Copy/Insert/ Move Constants Input column", ShortcutKey:="h" .MacroOptions macro:="FM_FilterDirList", Description:="FM Filter the Directory List", ShortcutKey:="k" .MacroOptions macro:="FM_CreateDirList", Description:="FM Create Directory List", ShortcutKey:="i" .MacroOptions macro:="DT_CopyLegend", Description:="FM Copy Legend", ShortcutKey:="l" .MacroOptions macro:="FM_LinkSheetInfo", Description:="FM Link Sheet Info", ShortcutKey:="L" .MacroOptions macro:="MapCells", Description:="Map Cells", ShortcutKey:="M" .MacroOptions macro:="ScreensClose", Description:="Close Screens", ShortcutKey:="n" .MacroOptions macro:="PswdRemove", Description:="Password Remove", ShortcutKey:="O" .MacroOptions macro:="DT_FormatSheets", Description:="DT Format Sheets", ShortcutKey:="P" .MacroOptions macro:="DT_PswdRemove", Description:="DT Passwords Remove", ShortcutKey:="Q" .MacroOptions macro:="ReplaceConstants", Description:="Replace Constants in Formulas", ShortcutKey:="R" .MacroOptions macro:="RemoveBorders", Description:="Remove Red Cell Borders", ShortcutKey:="r" .MacroOptions macro:="Foot_CrossFoot_Fix", Description:="Foot and CrossFoot Fix", ShortcutKey:="T" .MacroOptions macro:="ExtractConstantsInFormulas", Description:="Extract Constants in Formulas", ShortcutKey:="t" .MacroOptions macro:="PasswordsAllInternal", Description:="Clear all Passwords", ShortcutKey:="w" .MacroOptions macro:="ConsolCells", Description:="Consolidate Cells", ShortcutKey:="W" .MacroOptions macro:="Unhide_All", Description:="Unhide All", ShortcutKey:="X" .MacroOptions macro:="Screen", Description:="Add Second Screen", ShortcutKey:="y" .MacroOptions macro:="UsedRangeReset", Description:="Used Range Reset", ShortcutKey:="Z" End With End Sub In the ShortCutKey assignment above "k" is assigned to "FM_FilterDirList" with: .MacroOptions macro:="FM_FilterDirList", Description:="FM Filter the Directory List", ShortcutKey:="k" Via another Macro, I attempted to re-assign "k" to "XLPolySAP" but the assignment stayed FM_FilterDirList?? BTW, both macros are in the same module but named (and run) separately. What is happening, how can I avoid the issue in the future? TIA Dennis- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| keyboard shortcut to return to previous cell after "find" or "got. | New Users to Excel | |||
| Functions for "current" & "previous" month to calculate data | Excel Worksheet Functions | |||
| Formulate column to next "mmm-yy" from previous row "mmm-yy"? | Excel Discussion (Misc queries) | |||
| "Invalid property" after "Clear Form" | Excel Programming | |||
Linear Mode
