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


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

Who is Participating?
Rory ArchibaldConnect With a Mentor Commented:
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?
Rory ArchibaldCommented:
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?
Rory ArchibaldConnect With a Mentor Commented:
Possibly a more useful question: why do you need to disable macros if the user opens the workbook?
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

kazmdavAuthor Commented:
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.
kazmdavAuthor Commented:
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.
kazmdavAuthor Commented:
Such speedy & accurate responses. Right on the ball asking why I am doing what I'm doing. It led to delivering an excellent answer.!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.