Excel 2000 Duplicate Shortcut Keys

[Clayton Osterman]

I have a problem with duplicate shortcut keys, and I was wondering if anyone
knew any way around this. I have developed some personal macros that I use
control + a, d, or r to activate and these macros are stored on the
Personal.xls spreadsheet because I use them all the time. However, I have
just started working with a worksheet developed by a co-worker which uses all
of these keys as macros within the spreadsheet. From what I have read, the
first spreadsheet opened when there is a duplication of shortcut keys has the
priority, and since the Personal.xls spreadsheet is always first, my
shortcuts will always be called.

Since macros are supposed to make things easier, I was wondering if there is
an easy way to work around this conflict. Aside from redefining the shortcut
keys (which really isn't an option), the only way I can think of around this
is to unhide the personal.xls spreadsheet and close it everytime I need to
run one of the new spreadsheet macros. This seems like an simple annoyance,
but before I resign myself to this, is there any other way of getting around
this? I would hope for some kind of option like giving priority to specific
macros, but I haven't found anything like it.

Thanks for any information.

 

[Gary''s Student]

I am having a similar problem myself. In your case, let's assume that in
personal.xls you have 5 macros, named macro1, macro2, marco3, .....

Add another macro to personal.xls called short_killer:

Sub short_killer()
s = Array("macro1", "macro2", "macro3", "macro4", "macro5")
For i = 0 To 4
Application.MacroOptions Macro:=s(i), ShortcutKey:=""
Next
End Sub

1. start Excel (loads personal.xls and its macros)
2. run short_killer (clears the shortcuts from personal)
3. load your next workbook

 

[Clayton Osterman]

Thanks for the answer, this works wonderful. Here's the code I made if
anyone else would like to use it (I also added a new macro to reapply the
original shortcut keys after they have been removed):

Sub Personal_Macro_Down()
'This macro removes the shortcut keys from the above macros.

s = Array("ClearAll", "InsertDown", "InsertRight")
For i = 0 To 4
Application.MacroOptions Macro:=s(i), ShortcutKey:=""
Next
End Sub

Sub Personal_Macro_Up()
'This macro returns the shortcut keys to the Personal macros removed
above.

Application.MacroOptions Macro:="ClearAll", ShortcutKey:="a"
Application.MacroOptions Macro:="InsertDown", ShortcutKey:="d"
Application.MacroOptions Macro:="InsertRight", ShortcutKey:="r"

End Sub


Thanks again Gary's Student.