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

Hi,

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

kazmdavAsked:
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?
0
 
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?
0
 
Rory ArchibaldConnect With a Mentor Commented:
Possibly a more useful question: why do you need to disable macros if the user opens the workbook?
0
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.
0
 
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.
0
 
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.!
0
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.