Introduction
Sometimes Excel VBA behaves unexpectedly. Here is an example:
You are running code that is meant to open one or more files using
the Workbooks.Open command. As soon as you hold down the shift key when
this command is being processed, macro execution stops and your program
is terminated entirely. This will even happen if you are e.g. editing an
email message in Outlook whilst Excel VBA is processing the code in the
background.
Cause
Excel is designed not to run Auto_Open and Workbook_Open code when a
workbook is opened from the User interface whilst holding down the shift
key. Unfortunately, this (desired) behaviour also applies when opening
workbooks through VBA code.
Microsoft has confirmed this is a bug.
Resolution
Before opening a workbook through code, detect if the shift key is
pressed and wait until it is released. The code sample below shows you
how to do this:
Option Explicit
#If VBA7 Then
Declare PtrSafe
Function GetKeyState
Lib
"USER32" (ByVal vKey As
Long) As
Integer
#Else
Declare Function
GetKeyState Lib "USER32" (ByVal
vKey As Long)
As Integer
#End If
Const SHIFT_KEY As Long
= &H10
Function ShiftPressed()
As Boolean
'Returns True if shift key is pressed
ShiftPressed = GetKeyState(SHIFT_KEY) < 0
End Function
Sub Demo()
Do While
ShiftPressed()
DoEvents
Loop
Workbooks.Open Filename:="C:\My Documents\ShiftKeyDemo.xls"
End Sub
This problem has also been listed in the Microsoft knowledge base:
Macro in Excel Stops After A Workbook.Open Command, February 26,
2005