Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Open suppresses MsgBox Displays when Macro is run
Greetings! This problem has me really stumped. I am running VBA in Excel
2007. My operating system is Windows XP Pro. (The problem also occurs when the OS is Windows Vista.) The following VBA code runs perfectly when I run it in the VBE via F5, displaying both the message "BFROPEN", the message "AFROPEN", and the message "Finished!": Application.DisplayAlerts = True If Dir("C:\Documents and Settings\Mike\IIR\Book1.xlsm") < "" Then ' Book1.xlsm exists MsgBox "BFROPEN" Workbooks.Open ("C:\Documents and Settings\Mike\IIR\Book1.xlsm") MsgBox "AFROPEN" Else MsgBox "PROBLEM" End If MsgBox "Finished!" But if I include the above code in a VBA macro, assign the macro a hot key, and run it by typing the hot key, the message "BFROPEN" is displayed and file Book1.xlsm is opened, but the message "AFROPEN" is not displayed. The message "Finished" isn't displayed either. I suspect that there is something strange about the behaviour of Workbooks.Open when it is an instruction in a macro which is being executed. Any help or suggestions for further debugging will be extremely appreciated. May you have a blessed day. Sincerely, Michael D Fitzpatrick "EvangelMike" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Open suppresses MsgBox Displays when Macro is run
Hi Michael
Your code is working here in xl2000 and xl2007. I opened a workbook which started a macro on open, and I did get the two last messages when after focus was returned from the workbook_Open macro to the initial macro. BTW: I used a variable to hold the path and file name, rather than type it twice risking a typo. FileToOpen = "C:\Documents and Settings\Mike\IIR\Book1.xlsm" Application.DisplayAlerts = True If Dir(FileToOpen) < "" Then ' Book1.xlsm exists MsgBox "BFROPEN" Workbooks.Open (FileToOpen) MsgBox "AFROPEN" Else MsgBox "PROBLEM" End If MsgBox "Finished!" ----- HTH Per "MichaelDavid" skrev i meddelelsen ... Greetings! This problem has me really stumped. I am running VBA in Excel 2007. My operating system is Windows XP Pro. (The problem also occurs when the OS is Windows Vista.) The following VBA code runs perfectly when I run it in the VBE via F5, displaying both the message "BFROPEN", the message "AFROPEN", and the message "Finished!": Application.DisplayAlerts = True If Dir("C:\Documents and Settings\Mike\IIR\Book1.xlsm") < "" Then ' Book1.xlsm exists MsgBox "BFROPEN" Workbooks.Open ("C:\Documents and Settings\Mike\IIR\Book1.xlsm") MsgBox "AFROPEN" Else MsgBox "PROBLEM" End If MsgBox "Finished!" But if I include the above code in a VBA macro, assign the macro a hot key, and run it by typing the hot key, the message "BFROPEN" is displayed and file Book1.xlsm is opened, but the message "AFROPEN" is not displayed. The message "Finished" isn't displayed either. I suspect that there is something strange about the behaviour of Workbooks.Open when it is an instruction in a macro which is being executed. Any help or suggestions for further debugging will be extremely appreciated. May you have a blessed day. Sincerely, Michael D Fitzpatrick "EvangelMike" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Open suppresses MsgBox Displays when Macro is run
Remove the shift key from the hotkey assignment.
Holding down the shiftkey while you're opening a file tells excel not to run the workbook_open event or the auto_open procedure. And it confuses excel so that it never goes back to finish your code. MichaelDavid wrote: Greetings! This problem has me really stumped. I am running VBA in Excel 2007. My operating system is Windows XP Pro. (The problem also occurs when the OS is Windows Vista.) The following VBA code runs perfectly when I run it in the VBE via F5, displaying both the message "BFROPEN", the message "AFROPEN", and the message "Finished!": Application.DisplayAlerts = True If Dir("C:\Documents and Settings\Mike\IIR\Book1.xlsm") < "" Then ' Book1.xlsm exists MsgBox "BFROPEN" Workbooks.Open ("C:\Documents and Settings\Mike\IIR\Book1.xlsm") MsgBox "AFROPEN" Else MsgBox "PROBLEM" End If MsgBox "Finished!" But if I include the above code in a VBA macro, assign the macro a hot key, and run it by typing the hot key, the message "BFROPEN" is displayed and file Book1.xlsm is opened, but the message "AFROPEN" is not displayed. The message "Finished" isn't displayed either. I suspect that there is something strange about the behaviour of Workbooks.Open when it is an instruction in a macro which is being executed. Any help or suggestions for further debugging will be extremely appreciated. May you have a blessed day. Sincerely, Michael D Fitzpatrick "EvangelMike" -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Open suppresses MsgBox Displays when Macro is run
Greetings! Thanks for your reply. Unfortunately, I spent about 6 hours that
evening working on this problem, and the link in the email microsoft sent me notifying me of your reply just opened a blank page. Here is a response I had posted later that night (actually in the early morning!) on the mjtnet.com web site: "The problem described below has been solved. The VBA macro described below is called via a CTRL-SHIFT-P. But when the shift key is held down like this in calling any macro which has a Workbooks.Open command, the Workbooks.Open command is killed, and execution of the macro is stopped. The infuriating thing is that Microsoft has known about this problem since about the year 2000, and, apparently hasn't come up with a fix yet--All versions from Microsoft Excel 2000 up to the present are affected. (See Knowledge Base article ID 555263, last reviewed by Microsoft on 2/26/05.) The workaround: Do not use the shift key in any hotkey used to call any Excel macro which contains a Workbooks.Open command. This problem cost me about 6 hours of hard work, research, testing, striving for a workaround." -- Another one of my peeves is that Excel 2007 will allow you to assign the same hotkey to numerous macros. When you then perform that hotkey, Excel just performs the first macro it comes to with that hotkey assigned to it. When assigning a hotkey to a macro, Excel should check if that hotkey has already been assigned to a macro. May you have a most blessed day! Sincerely, Michael Fitzpatrick "Dave Peterson" wrote: Remove the shift key from the hotkey assignment. Holding down the shiftkey while you're opening a file tells excel not to run the workbook_open event or the auto_open procedure. And it confuses excel so that it never goes back to finish your code. MichaelDavid wrote: Greetings! This problem has me really stumped. I am running VBA in Excel 2007. My operating system is Windows XP Pro. (The problem also occurs when the OS is Windows Vista.) The following VBA code runs perfectly when I run it in the VBE via F5, displaying both the message "BFROPEN", the message "AFROPEN", and the message "Finished!": Application.DisplayAlerts = True If Dir("C:\Documents and Settings\Mike\IIR\Book1.xlsm") < "" Then ' Book1.xlsm exists MsgBox "BFROPEN" Workbooks.Open ("C:\Documents and Settings\Mike\IIR\Book1.xlsm") MsgBox "AFROPEN" Else MsgBox "PROBLEM" End If MsgBox "Finished!" But if I include the above code in a VBA macro, assign the macro a hot key, and run it by typing the hot key, the message "BFROPEN" is displayed and file Book1.xlsm is opened, but the message "AFROPEN" is not displayed. The message "Finished" isn't displayed either. I suspect that there is something strange about the behaviour of Workbooks.Open when it is an instruction in a macro which is being executed. Any help or suggestions for further debugging will be extremely appreciated. May you have a blessed day. Sincerely, Michael D Fitzpatrick "EvangelMike" -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Open suppresses MsgBox Displays when Macro is run
If you're going to hang around the newsgroups, you may want to avoid the web
page interface (if you can). Saved from a previous post: If you have Outlook Express installed, try clicking on these links (or copy and paste into MSIE). news://msnews.microsoft.com/microsof...ic.excel.setup news://msnews.microsoft.com/microsoft.public.excel.misc news://msnews.microsoft.com/microsof...heet.functions news://msnews.microsoft.com/microsof...excel.newusers news://msnews.microsoft.com/microsof...el.programming (and a few more for MSWord) news://msnews.microsoft.com/microsof....docmanagement news://msnews.microsoft.com/microsof...word.word97vba news://msnews.microsoft.com/microsof....word.newusers news://msnews.microsoft.com/microsof...ord.pagelayout news://msnews.microsoft.com/microsof...ord.vba.addins news://msnews.microsoft.com/microsof....vba.beginners news://msnews.microsoft.com/microsof....customization news://msnews.microsoft.com/microsof...rd.vba.general news://msnews.microsoft.com/microsof....vba.userforms news://msnews.microsoft.com/microsof....word6-7macros (You can always connect to more later) Here are some links that explain it better: Chip Pearson has some notes written by Leonard Meads at: http://www.cpearson.com/excel/DirectConnect.htm David McRitchie's notes at: http://www.mvps.org/dmcritchie/excel/xlnews.htm http://www.mvps.org/dmcritchie/excel/oe6.htm http://www.mvps.org/dmcritchie/excel/oe6nws01.htm Tushar Mehta's notes at: http://www.tushar-mehta.com/misc_tut...e_ng/index.htm And if you're looking for old posts: Or you can use google (maybe a few hours behind) to search for stuff you've posted (and find the replies, too) http://groups.google.com/advanced_group_search http://groups.google.com/advanced_gr...Excel*&num=100 Ron de Bruin has an excel addin that you may like: http://www.rondebruin.nl/Google.htm MichaelDavid wrote: Greetings! Thanks for your reply. Unfortunately, I spent about 6 hours that evening working on this problem, and the link in the email microsoft sent me notifying me of your reply just opened a blank page. Here is a response I had posted later that night (actually in the early morning!) on the mjtnet.com web site: "The problem described below has been solved. The VBA macro described below is called via a CTRL-SHIFT-P. But when the shift key is held down like this in calling any macro which has a Workbooks.Open command, the Workbooks.Open command is killed, and execution of the macro is stopped. The infuriating thing is that Microsoft has known about this problem since about the year 2000, and, apparently hasn't come up with a fix yet--All versions from Microsoft Excel 2000 up to the present are affected. (See Knowledge Base article ID 555263, last reviewed by Microsoft on 2/26/05.) The workaround: Do not use the shift key in any hotkey used to call any Excel macro which contains a Workbooks.Open command. This problem cost me about 6 hours of hard work, research, testing, striving for a workaround." -- Another one of my peeves is that Excel 2007 will allow you to assign the same hotkey to numerous macros. When you then perform that hotkey, Excel just performs the first macro it comes to with that hotkey assigned to it. When assigning a hotkey to a macro, Excel should check if that hotkey has already been assigned to a macro. May you have a most blessed day! Sincerely, Michael Fitzpatrick "Dave Peterson" wrote: Remove the shift key from the hotkey assignment. Holding down the shiftkey while you're opening a file tells excel not to run the workbook_open event or the auto_open procedure. And it confuses excel so that it never goes back to finish your code. MichaelDavid wrote: Greetings! This problem has me really stumped. I am running VBA in Excel 2007. My operating system is Windows XP Pro. (The problem also occurs when the OS is Windows Vista.) The following VBA code runs perfectly when I run it in the VBE via F5, displaying both the message "BFROPEN", the message "AFROPEN", and the message "Finished!": Application.DisplayAlerts = True If Dir("C:\Documents and Settings\Mike\IIR\Book1.xlsm") < "" Then ' Book1.xlsm exists MsgBox "BFROPEN" Workbooks.Open ("C:\Documents and Settings\Mike\IIR\Book1.xlsm") MsgBox "AFROPEN" Else MsgBox "PROBLEM" End If MsgBox "Finished!" But if I include the above code in a VBA macro, assign the macro a hot key, and run it by typing the hot key, the message "BFROPEN" is displayed and file Book1.xlsm is opened, but the message "AFROPEN" is not displayed. The message "Finished" isn't displayed either. I suspect that there is something strange about the behaviour of Workbooks.Open when it is an instruction in a macro which is being executed. Any help or suggestions for further debugging will be extremely appreciated. May you have a blessed day. Sincerely, Michael D Fitzpatrick "EvangelMike" -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run Macro In All Open Workbooks | Excel Discussion (Misc queries) | |||
Macro to merge open workbooks | Excel Discussion (Misc queries) | |||
Excel 2003 Workbooks.Open with CorruptLoad=xlRepairFile fails on Excel 5.0/95 file due to Chart, with Error 1004 Method 'Open' of object 'Workbooks' failed | Excel Programming | |||
Open two new workbooks with macro | Excel Programming | |||
help with macro to open and close workbooks | Excel Programming |