As a fulltime developer I oftentimes open files containing VBA and want to be able to run code. At the same time I sometimes want to prevent Workbook_Open event code or an Auto_Open macro from running.
With Excel 97 to 2003, this was simple: hold the shift key when you click “Enable macros”.
With Excel 2007 and 2010 it is less obvious how to do this, holding shift whilst clicking the enable button does not help. In the article mentioned below I have detailed out the exact steps required to prevent these macros from running under all possible circumstances.
Link: Preventing auto_open and Workbook_Open events from running
Enjoy the read and let me know what you think!!
Regards,
Jan Karel Pieterse
www.jkp-ads.com
Useful article – thank you!
Not sure what I’m doing wrong, but neither the File | Open or MRU method is working for me in Excel 2007 to prevent Workbook_Open macros from running in a workbook placed in a trusted location.
I do have several add-ins that are started up when Excel loads so perhaps one of those is causing the problem.
My practice is to include a line like
Application.Wait (Now + TimeValue(“0:00:10?))
at the first of the Workbook_Open event to allow the user to Ctrl-Break and halt the macro execution, but of course that delay affects the user experience…
I must admit I did not test the trusted folders variation of the theme. Will do though.
I just checked. Added a trusted folder to Excel, saved a workbook with a Workbook_Open routine to that location, closed and reopened Excel to be sure and used the technique described in the article, worked without a hitch: with shift down, the open event does not fire. Either from MRU or from FIle, Open.
Maybe a SP difference?
Running Vista SP1 and Excel 2007 12.0.6514.5000 with SP1 MSO 12.06320.5000. However, this is in a corporate environment, so who knows what modifications/extensions have been added to the corporate image that might be affecting this.
Active addins are:
Analysis ToolPak
Analysis ToolPak-VBA
Chart Advisor from Microsoft Office Labs
Microsoft Office Excel 2007 Get Started Tab
PIBVExcel (OSISoft addin)
PI-DataLink (OSISoft addin)
Reporting Tools (locally developed add-in)
Scoutcommands
Search Commands from Microsoft Office Labs
SnagIt Add-In
Solver Add-In
I realize this probably has become a curiosity item, but I’m not sure trying to run down the cause is worth your time – especially if no one else reports a problem with your solution…
Sure, but it is good to know there may be exceptions. Thanks for the details anyway!
Maybe useful for one’s own workbooks, but it seems a bit dangerous for files from others. If macros can run, then presumably event handlers can run. Actually I just tested that in Excel 2003. It appears event handlers don’t run until after either I run a macro interactively or a formula calling a udf recalcs.
Even so, I don’t see why a developer wouldn’t just use a special purpose macro to open such workbooks via code:
On Error GoTo CleanUp
Application.EnableEvents = False
Workbooks.Open Filename:=“bar.xls”
CleanUp:
Application.EnableEvents = True
End Sub
Setting EnableEvents to False prevents Workbook_Open from running (as well as other event handlers), and Excel never runs Auto_Open macros when opening workbooks via VBA. Wouldn’t this also work under Excel 2007 and later?
fzz: I agree, this is not meant as a measure against malicious code, it is meant as a way to open a workbook and be able to prevent lengthy initialisation code from running, while at the same time being able to debug code in the file.
I never knew event handlers do not fire until a macro has been run or a udf calcs! I’ll add that info too.