How do I disable workbook macros if opened by a user but NOT disable if opened via VBA

Posted on 2011-04-20
Last Modified: 2012-08-13

I need to disable macros if a workbook is opened by a user (referred to as '1st workbook') but I don't want the macros disabled if the workbook is opened via VBA code from another workbook (referred to as '2nd workbook').

See the code 'workbook_open' that I currently have (in '1st workbook'), which disables the macros & works fine when opened by a user.

To get around this 'workbook_open' code when I open '1st workbook' via the VBA code in '2nd workbook', I have the following code "From 2nd workbook". However, disabling events code in '2nd workbook' doesn't work because the macros in '1st workbook' are disabled when the '2nd workbook' opens it..  

I suspect that my problem is that the 'Application.EnableEvents = False' is only applied to the activeworkbook and there not applied to '1st workbook' (because it is not yet opened). Thus the 'Workbook_Open' event in '1st workbook' is still executed upon it's opening.

Hmm, maybe I need to state which workbook to apply 'Application.EnableEvents = False' to? If so, can anyone please recommend the best way to do that please?
 Or any other suggestions please?

' From 1st workbook
Sub Workbook_Open()
Dim secAutomation As MsoAutomationSecurity
secAutomation = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityForceDisable
End sub

Open in new window

' From 2nd workbook
Application.EnableEvents = False 
Workbooks.Open filename 
Application.EnableEvents = True

Open in new window

Question by:kazmdav
    LVL 85

    Expert Comment

    by:Rory Archibald
    Application.EnableEvents is the correct setting and it is application-wide, not workbook specific.
    Are you resetting the automationsecurity level after you set it to disable?
    LVL 85

    Assisted Solution

    by:Rory Archibald
    Possibly a more useful question: why do you need to disable macros if the user opens the workbook?

    Author Comment

    Firstly, in response to your 2nd question:
       I need to disable macros when user opens the 1st workbook because I don't want the user to see my custom menu & cause attempts to run anything from it.

       However, if there's another way that I can suppress this custom menu when the user opens the 1st workbook, then I will do that. Or, if there's another way that I can cause the custom menu to be initiated via my VBA code (from 2nd workbook) then I will do that.

    Secondly, in response to your 1st question:
       No, I am not resetting the automationsecurity level after i disable it. Without researching, I don't really yet know how to do that.
    LVL 85

    Accepted Solution

    You could remove your menu building code from the open event and make it a sub in the workbook that you can then call when you open the workbook programmatically. Alternatively it would seem to make more sense to put the code in a different workbook if you don't want the user to use it?

    Author Comment

    Oh! Of course, what a good idea... I will move my menu building code & do as you said, that will work.  Thank you, that is such an easy, logical solution. :-)

    FYI:  I am trying to use the workbook for 2 different types of users, but with common goals. One of the user types (the type coming from the 2nd workbook) requires access to the custom menu in 1st workbook.

    Author Closing Comment

    Such speedy & accurate responses. Right on the ball asking why I am doing what I'm doing. It led to delivering an excellent answer.!

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    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 …
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    754 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

    19 Experts available now in Live!

    Get 1:1 Help Now