VBA code in multiple workbooks

[Jon]

Hi,

I have the same VBA code (invoked via accelerator keys) in two
workbooks.

I load the first workbook, then load the 2nd workbook and execute
macros in it. I then unload the second workbook and then execute a
macro (the same one) from within the first workbook -- and it wants to
reload the 2nd workbook again.

A broader question is etiquette for sharing accelerator keys with
other applications. How to handle conflicts and/or leave the keys in
the prior state when unloading.

Thanks in advance for any help -- I'd bet this has been discussed
before, but I can't seem to find it.

- Jon

 

[John Tjia]

You can make the macro stick to the workbook you are using, even if
you have multiple workbooks open with the same macro, by using this
code:

Sub MacroAccel() 'Lanuch this with Ctrl+letter or Ctrl+Shift+letter
Application.Run macro:="'" & ThisWorkbook.Name &
"'!Module1.YourMacro"
End Sub

rather than just using

Sub MacroAccel()
Module1.YourMacro
End Sub

As for the etiquette question, I don't have a particular approach, but
I tend to use the Ctrl+Shift+letter combination, because there are no
native Excel accelerators using the Shift letter combination, and not
many homegrown accelerator keys use that either, leading to less
chances for conflicts.