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

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

0
kazmdav
Asked:
kazmdav
  • 3
  • 3
2 Solutions
 
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 ArchibaldCommented:
Possibly a more useful question: why do you need to disable macros if the user opens the workbook?
0
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Rory ArchibaldCommented:
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
 
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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