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