Workbooks.Open suppresses MsgBox Displays when Macro is run

[MichaelDavid]

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"

 

[Per Jessen]

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!"

 

[Dave Peterson]

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]

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]

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/microsoft.public.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions
news://msnews.microsoft.com/microsoft.public.excel.newusers
news://msnews.microsoft.com/microsoft.public.excel.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsoft.public.word.docmanagement
news://msnews.microsoft.com/microsoft.public.word.word97vba
news://msnews.microsoft.com/microsoft.public.word.newusers
news://msnews.microsoft.com/microsoft.public.word.pagelayout
news://msnews.microsoft.com/microsoft.public.word.vba.addins
news://msnews.microsoft.com/microsoft.public.word.vba.beginners
news://msnews.microsoft.com/microsoft.public.word.vba.customization
news://msnews.microsoft.com/microsoft.public.word.vba.general
news://msnews.microsoft.com/microsoft.public.word.vba.userforms
news://msnews.microsoft.com/microsoft.public.word.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_tutorials/oe_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_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm