Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypass Auto_Open
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypass Auto_Open
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypass Auto_Open
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypass Auto_Open
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypass Auto_Open
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. -- Jac Tremblay "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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypass Auto_Open
Dim Verify as Long
Jac Tremblay wrote: 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. -- Jac Tremblay "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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypass Auto_Open
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 run. I'm guessing that you release the shift key too soon. 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypass Auto_Open
Oops, I was wrong. I use this
Dim Verify As VbMsgBoxResult Sorry about that. Barb Reinhardt "Jac Tremblay" wrote: 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. -- Jac Tremblay "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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypass Auto_Open
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 "Barb Reinhardt" wrote: Oops, I was wrong. I use this Dim Verify As VbMsgBoxResult Sorry about that. Barb Reinhardt "Jac Tremblay" wrote: 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. -- Jac Tremblay "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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypass Auto_Open
Hi Dave,
It works fine with your solution as well. Thanks a lot. -- Jac Tremblay "Dave Peterson" wrote: Dim Verify as Long Jac Tremblay wrote: 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. -- Jac Tremblay "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 -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypass Auto_Open
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypass Auto_Open
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 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" wrote: 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 run. I'm guessing that you release the shift key too soon. 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 -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypass Auto_Open
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. Brian wrote: 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 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" wrote: 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 run. I'm guessing that you release the shift key too soon. 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 -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypass Auto_Open
Just one of those "anomalies" we developers love to talk about, I guess...
"Dave Peterson" wrote: 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. Brian wrote: 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 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" wrote: 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 run. I'm guessing that you release the shift key too soon. 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bypass Auto_open Macro in Excel 2007 | Excel Discussion (Misc queries) | |||
Holding Shift to bypass Auto_Open doesn't work in Excel 2007... | Excel Programming | |||
how to bypass password?? | Excel Programming | |||
Bypass Worksheet_Change Sub | Excel Programming | |||
Bypass an | Excel Programming |