Disable Macros when opening workbook

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

How do i disable macros when opening a workbook in code?

I have a workbook that creates a new menu when it opens
and destroys the menu when it closes. One of the choices
in that menu is to open an older version of that same
workbook and import its data and then close that workbook.
When it closes, it destroys the wrong menu. How do i solve
this problem?

One way would be to disable macros on the workbook that is
opened in code. Another would be to temporarily set macro
security to high. Any other ideas?
 
You can disable events

Application.EnableEvents = False
workbooks.Open Filename:="myworkbook.xls"
Application.EnableEvents = True
 
How can I d o this "temporarily set macro security to high" in VBA ??

regards , Jeroen
 
If you use the code Tom suggested, you won't have to.

And turn off events when you close the workbook, too.
 
I am looking for an answer to the original question.

I have a worksheet containing a list of hundreds of users' workbooks on
a number of network servers. I want to identify which contain VBA code
and which don't. Later it will be my job to test that they run in our
new company environment (Office 97 -> Office XP; Windows 2000 ->
Windows XP). This is only the first of such lists, and will be
looking at potentially thousands of workbooks over the next couple of
months.

Rather than open them all up manually, I wrote a simple procedure to
scan through the list of paths, open each workbook, sum up the
CountOfLines in each CodeModule, then close the workbook. I will run
this overnight, and come back tomorrow and focus on the workbooks that
contain vba code.

I do not want to execute macros when I open each workbook.
OK... Solution : turn off events as suggested above

This worked fine, until my application started opening workbooks with
references to some obscure fiancial libraries that some users have
installed that I don't have. The Workbook_Open doesn't run, but Excel
still tries to COMPILE the code. The compilation fails, shows me the
errant line of code in the user's workbook. Neither disable events nor
disable alerts stops this. I really want to disable macros completely
when I open up each workbook. But I still want to be able to count how
many lines of code....
 
use
Application.AutomationSecurity = msoAutomationSecurityForceDisable





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


ScottDK wrote :
 
keepITcool.
Only for Office XP and up ?
As my Office2K does not support that.

NickHK
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Unwanted workbooks on startup 2
Opening a list of Excel files and run a macro in each 17
Copy & Paste from One Workbook to a Macroed Template Workbook 1
Macros disabled, not by security settings? 5
How to Unlock Excel Workbook protected by MACRO Password 6
COM addin won't connect when workbook opened via another workbookfrom another instance 3
VBA Debug Issue 2
closing another file by code causes failure to run workbook activate event 2

Back
Top