Excel VBA - Restrict Macro to its workbook

Posted on 2012-08-18
Last Modified: 2012-08-19
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.
Question by:AndresHernando
    LVL 24

    Assisted Solution

    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.
    LVL 17

    Accepted Solution

    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


    Author Comment

    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.
    LVL 24

    Expert Comment

    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.

    Author Comment

    OK.  Thanks again for your help!  --Andres

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now