Remove all keyboard shortcut keys assigned to macros - an example

A

aztecbrainsurgeon

Hello,
No question here, just a procedure example for archive.

REMOVE ALL KEYBOARD SHORTCUT KEYS ASSIGNED TO MACROS IN EXCEL WORKBOOK
MODULES
an example:

Sub MacroKeyBoardShortcutsRemoveAll()
Dim li_CurrentLine As Integer
Dim li_ArguementsStart As Integer
Dim WbName, MacroName, FullName As String
Dim ls_Line As String
Dim l_Component As Object

On Error Resume Next
MacroName = ""
FullName = ""

' Look at each VB Component (form/class/module) in turn

For Each l_Component In Workbooks(1).VBProject.VBComponents

' Only look at modules. Other types are: 2=Class,
3=Form,100=Worksheet

If l_Component.Type = 1 Then

' Work through each line of code in turn

For li_CurrentLine = 1 To
l_Component.CodeModule.CountOfLines
ls_Line = l_Component.CodeModule.Lines(li_CurrentLine,
1)

' Remove spaces from the start in case of indentation

ls_Line = Trim$(ls_Line)

' See if this line is what we want.

If Left$(ls_Line, 3) = "Sub" Then
li_ArguementsStart = InStr(ls_Line, "()")
If li_ArguementsStart > 0 Then

MacroName = "!" & Trim$(Mid$(ls_Line, 4,
li_ArguementsStart - 4))
WbName = Workbooks(1).Name
FullName = WbName & MacroName
'This line below removes the keyboard
shortcuts. You may also
'delete all descriptions by adding after macro
name reference: Description:=""

Application.MacroOptions Macro:=FullName,
ShortcutKey:=""

End If

End If

Next li_CurrentLine
End If
Next l_Component
End Sub





Search Criteria:
Remove all keyboard shortcuts in workbook
Delete all keyboard shortcut keys in Excel
Purge keyboard short cut keys
Reset keyboard shortcut keys
Remove ShortcutKey assignments references
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top