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.

"Barb Reinhardt" wrote:

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.



"Brian" wrote:

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 Auto_Open()
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

"Dave Peterson" wrote:

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 wrote:

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:

Sub Auto_Open()
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