Bypass Auto_Open

[Brian]

I think that I should be able to bypass an Autpen macro by holding down
Shift while opening the workbook. However, I have this Autpen macro:

Sub Autpen()
Workbooks.Open FileName:="C:\MyFile.xls"
Rows("1:14").Select
Selection.Delete Shift:=xlUp
Selection.End(xlDown).Select
ActiveCell.EntireRow.Delete
Selection.End(xlUp).Select
ActiveCell.EntireRow.Delete
Range("A1").Select
ActiveWorkbook.SaveAs FileName:="C:\MyFile2.xls", FileFormat:=xlNormal,
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
CreateBackup:=False
ActiveWindow.Close
'close Excel
Application.Quit
End Sub

Even when I hold down Shift, the macro runs the first line:

Workbooks.Open FileName:="C:\MyFile.xls"

How can I keep it from running even this much when I open the workbook
having the macro in it?

 

[Dave Peterson]

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?

 

[Brian]

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
code, etc.

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:

Sub Autpen()
MsgBox "This is a test."
MsgBox "This is a test2."
Workbooks.Open FileName:="C:\MyFile.xls"
THE MACRO STOPS HERE AND DOES NOT PROCEED
Rows("1:14").Select
Selection.Delete Shift:=xlUp
Selection.End(xlDown).Select

 

[Barb Reinhardt]

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
auto-open

Dim Verify as MSOMsgBoxResult '<~~~I think that's it

Verify = MsgBox("Do you want to do XYZ?",vbyesno)

If Verify = VBNo then exit sub


--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

 

[Jac Tremblay]

Hi Barb,
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.

Thanks.

 

[Dave Peterson]

Dim Verify as Long

Hi Barb,
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.

Thanks.

 

[Dave Peterson]

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 autpen procedure didn't
run.

I'm guessing that you release the shift key too soon.

 

[Barb Reinhardt]

Oops, I was wrong. I use this

Dim Verify As VbMsgBoxResult

Sorry about that.

Barb Reinhardt

 

[Jac Tremblay]

Thanks Barb, you make my day.
It works fine and it is a good idea that I am sure many users will benefit
from.
I love you.

 

[Jac Tremblay]

Hi Dave,
It works fine with your solution as well.
Thanks a lot.

 

[Brian]

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 Autpen 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.

 

[Brian]

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
throughout).

It does, however, work correctly and completely bypass the Autpen 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]

I don't have a guess why it doesn't work for you.

It worked for me in my tests and has always worked in the past.

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
throughout).

It does, however, work correctly and completely bypass the Autpen 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.

 

[Brian]

Just one of those "anomalies" we developers love to talk about, I guess...