Macro in Excel Stops After A Workbook.Open Command

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

'Declare API
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
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

Comments

All comments about this page:

Comment by: Bob (9/11/2009 8:51:18 AM)

A commentor named Phil wrote that he is doing a consolidation project where he opens a string of workbooks, extracts data, then moves on. I am doing the same but my problem is that each of the workbooks I am opening has VBA code that is running automatically each time the workbook is opened. My consolidation program therefore gets stopped when the workbook that I open starts it's auto code. I've tried to hold the shift key to not run the auto code but this does not always work. Is there a way to do workbooks.open and tell the workbook being opened to not run it's auto code?

Comment by: Jan Karel Pieterse (9/11/2009 10:13:39 AM)

Hi Bob,

Sure, before issuing the Open command, insert this line:

Application.EnableEvents = False

Do not forget to turn them back on, because otherwise all events stay disabled for the entire Excel session:

Application.EnableEvents = True

Comment by: Charmene (9/28/2009 10:46:57 AM)

I am having trouble when I open Excel. First I was entering a Macro and I stored it on my personal worksheet and hide it. But now when ever I open Excel it opens with 33 active worksheets. I have no clue as to why or how to get rid of them. I have uninstalled the program and re-installed it but I am getting the same thing. Any ideas?

Thanks

Comment by: Jan Karel Pieterse (9/28/2009 10:49:26 AM)

Hi Charmene,

Looks like you have entered a path in Excel's options:

Tools, options, General tab, "At startup, open all files in"

Excel will open all files in that location.

Comment by: Tom Parish (11/5/2009 7:46:19 AM)

It took a bit of lateral thinking but based on the above....

Comment by: Phil (8/20/2009 1:34:30 AM)
Comment by: Jan Karel Pieterse (9/7/2009 9:45:42 AM)

....I inserted the loop code right at the start of the procedure and this gave it enough time to register the shift release before doing wb.open. I presume this wouldn't work if my wb.open command was right at the start of the code. Evidently, I need a slower PC.

Comment by: John (11/25/2009 12:42:13 PM)

Great idea--thanks for posting it.

Excel is very, very weird sometimes.

Often.

Comment by: Rene Musters (4/1/2010 4:42:05 AM)

Guess what, You have identified a long old problem I have, where the open statement stops VB.
HOWEVER, in my situation it is the other way around. I MUST press the shift key to get it working properly.

Here is the code. Any idea WHY? Keyboard works normal, pressing shift provides uppercase, and capslock is uppercase
Never have problem entering logon password etc.

Public Sub OpenFilename(FilePathToOpen)
    ' bug! stops further processing after the open
    ' However, works reverse here. MUST press shift
    Do While Not ShiftPressed()
        DoEvents
        MsgBox "Hold the <Shift> key, and press <Enter>"
    Loop
    Workbooks.Open FileName:=FilePathToOpen
End Sub

Comment by: Jan Karel Pieterse (4/1/2010 4:51:59 AM)

Hi Rene,

I have NEVER seen this before!

What Excel version are you using?

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.