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.
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.
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:
Declare Function GetKeyState Lib "User32" _
(ByVal vKey As Integer) As Integer
Const SHIFT_KEY = 16
Function ShiftPressed() As Boolean
'Returns True if shift key is pressed
ShiftPressed = GetKeyState(SHIFT_KEY) < 0
Do While ShiftPressed()
Workbooks.Open Filename:="C:\My Documents\ShiftKeyDemo.xls"
This problem has also been listed in the Microsoft knowledge base:
Macro in Excel Stops After A Workbook.Open Command, February 26, 2005