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
|