Limiting the scope of macros and associated shortcut keys

[Chris Watts]

I have a bunch of workbooks that are open simulateously. One of them
contains two worksheets, the others just one each.

I have written lostsof vba macros but would like to limit their visibility
to the workbook in which they exist. How can I achieve this?

Each macro does have a shortcut key defined using the Application.Onkey
method. I would like to limit the scope of the shortcut key to the workbook
in which it is defined - so that the same shortcut will call different
macros depending on which workbook is active. How can I achieve this?

Ideas would be greatly appreciated.

cheers
Chris

 

[Gord Dibben]

I would place all the macros in Personal.xls or an Add-in..........

Then write the macro to point to the ActiveWorkbook.

Sub sheets_num()
MsgBox ActiveWorkbook.Sheets.Count & " sheets in workbook"
End Sub

No need for Application.OnKey in that case, just a shortcut key to run the
macro.


Gord Dibben MS Excel MVP

 

[Chris Watts]

Thanks Gord. I'll need to think that one through with my current situation.
cheers
Chris