VBA code in multiple workbooks

  • Thread starter Thread starter Jon
  • Start date Start date
J

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
 
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.
 
Back
Top