• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1786
  • Last Modified:

Excel VBA - Restrict Macro to its workbook

How can I make a macro be restricted ONLY to its workbook?

I have macros in a workbook that have short-cut keys to call them, but I don't want them to be called from other open workbooks.
0
AndresHernando
Asked:
AndresHernando
  • 2
  • 2
2 Solutions
 
SteveCommented:
You could go for the following code at the start of the module you don't want it to run in:

If ActiveWorkbook <> ThisWorkbook then exit sub

Open in new window


This will stop the code from running with any other workbook than the one with the code in it.
0
 
andrewssd3Commented:
The approach the_barman suggests is probably simplest, but it won't quite work in that form - you need to say
    If Not (ThisWorkbook Is ActiveWorkbook) Then Exit Sub

Open in new window

0
 
AndresHernandoAuthor Commented:
Thanks for your input Barman and Andrew.  This helps. But... is there a way to restrict the scope of the shortcut keys calling macros to the active  workbook?

I want to be able to use the same shortcut keys for different macros, each in separate workbooks.

For example, I have assigned "Cntrl+Shift-A" to two different macros, each macro in a separate workbook.  However, when I enter "Cntrl+Shift+A" it only calls the macro of the workbook that was opened first in the Excel session, not the macro in the active workbook.
0
 
SteveCommented:
I am sorry to say that it is not possible to declare the shortcut key specific to the workbook which is active. So other than changing the shortcut key, or making sure that the macro will not run in the wrong file, there is not a lot to be done.
0
 
AndresHernandoAuthor Commented:
OK.  Thanks again for your help!  --Andres
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now