Excel - Bypass Auto_Open
Asked By Bria on 03-Oct-08 09:04 PM
I think that I should be able to bypass an Auto_Open macro by holding down
Shift while opening the workbook. However, I have this Auto_Open macro:
ActiveWorkbook.SaveAs FileName:="C:\MyFile2.xls", FileFormat:=xlNormal,
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
Even when I hold down Shift, the macro runs the first line:
How can I keep it from running even this much when I open the workbook
having the macro in it?
Dave Peterson replied on 03-Oct-08 09:26 PM
When I use file|open to select the file to open, then hit and hold the shift key
while clicking on the Open button, then clicking the "yes" button (to allow
macros--my security setting) while continuing to hold down the shift button, the
auto_open procedure doesn't run.
What steps are you using where the code runs?
Bria replied on 03-Oct-08 09:45 PM
It works the way you described when clicking File -> Open. However, I
normally highlight the file name in Windows Explorer, hold down Shift, then
Press the Enter key. I have macro security set to Low to enable simple
Access integration, so there is no intervening security message.
Curiously, it does not run the entire macro when I do this - only the first
line where it opens the other file. It does not proceed to the row selection
Also, curiously, when I add a MsgBox (or more than one) before the first
line in the macro, it runs the MsgBox AND the first line:
MsgBox "This is a test."
MsgBox "This is a test2."
THE MACRO STOPS HERE AND DOES NOT PROCEED
BarbReinhard replied on 03-Oct-08 11:07 PM
I have a workbook that I sometimes want to execute the code on and sometimes
not when I open it. I've put something like this in the beginning of the
Dim Verify as MSOMsgBoxResult '<~~~I think that's it
Verify = MsgBox("Do you want to do XYZ?",vbyesno)
If Verify = VBNo then exit sub
If this post was helpful to you, please click YES below.
jac.trembla replied on 04-Oct-08 01:16 AM
I get an error on MSOMsgBoxResult when I try to compile the project:
User-defined type not defined.
Which reference should I add to my project?
I use Excel 2007.
Dave Peterson replied on 04-Oct-08 08:21 AM
Dim Verify as Long
Dave Peterson replied on 04-Oct-08 08:25 AM
I tried your method (xl2003 on win xp home).
I held the shift key until the file was open--I didn't release it.
If excel was closed, I got a few messages that my addins wouldn't open. But my
workbook opened fine and the auto_open procedure didn't run.
If excel was open, my workbook opened fine and the auto_Open procedure didn't
I'm guessing that you release the shift key too soon.
BarbReinhard replied on 04-Oct-08 08:56 AM
Oops, I was wrong. I use this
Dim Verify As VbMsgBoxResult
Sorry about that.
jac.trembla replied on 04-Oct-08 10:26 AM
Thanks Barb, you make my day.
It works fine and it is a good idea that I am sure many users will benefit
I love you.
jac.trembla replied on 04-Oct-08 10:30 AM
It works fine with your solution as well.
Thanks a lot.
Bria replied on 04-Oct-08 02:27 PM
I know I could do that; however, it is not worth it in this case, since the
macro does not progress far enough to do any harm before it stops. The only
reason I want to bypass the Auto_Open is to modify the macro; it includes
code that closes the workbook when done, so if I do not bypass it, I have no
way of editing the macro.
I am mainly curious as to why the bypass key does not work correctly. This
workbook is never opened directly by the user from Excel. Instead, I Shell to
Excel, using the filename as an argument, from VBA in Access to remove
non-data lines, thus making it it more palatable for the Access import, or at
least easier for the lazy programmer (me) to deal with in Access.
Bria replied on 04-Oct-08 03:10 PM
I'm not sure how I can be releasing the shift key too soon when I still have
it held down after the first line of the macro opens the other workbook...
It does the same thing from three different computers.
I do find that it does not happen if any other function intervenes. For
example, if I have macro security set to anything except low, the security
notification message seems to intervene and prevent the macro from running
after I approve the running of macros (i.e. if I keep the shift key down
It does, however, work correctly and completely bypass the Auto_Open macro
if I click File -> Open from within Excel.
It's really not that big a deal. I was just curious about why the bypass key
does not work completely. It does not happen with Excel 2000, but it does
happen with various computers having Excel 2003, both SP2 & SP3.
Dave Peterson replied on 04-Oct-08 04:14 PM
I do not have a guess why it does not work for you.
It worked for me in my tests and has always worked in the past.
Bria replied on 04-Oct-08 04:23 PM
Just one of those "anomalies" we developers love to talk about, I guess...