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

#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

Comments

Showing last 8 comments of 55 in total (Show All Comments):

Comment by: Hasan (2-4-2015 09:51:40) deeplink to this comment

Hi,

I ran into this issue while trying to re-open 'ThisWorkbook' (i.e the caller-workbook from its own code).

I need to revert the file back to last saved version within the before-close event I trapped, where the user choses not to save the changes. As my code hides all sheets but one before close, I need to save the file though, but I can do this only for the last-saved-version.

I thought this issue of workbook.open / auto_open not working was related to opening the 'caller-workbook' but as you describe it might not be the real source.

Also any code after the workbooks.open command in the caller-workbook do not get executed, so I have no way of executing other commands (except with application.onTime which I don't want to use, as it does not work when app is closed and not only the wbk).

Unfortunaly in my case it has nothing to do with Shift key so checking if pressed will not help.

If you have any ideas how to work around, please let me know.

Hasan
(using Win7 / XL2007)

Comment by: Jan Karel Pieterse (2-4-2015 10:28:07) deeplink to this comment

Hi Hasan,

I guess what you could try to do is:

- Save-as to a temporary name in a fixed location
- Open the previous copy
- Close the temporary file
- Have some code in the original which tries to kill the temporary file.

Comment by: Hung (26-3-2016 04:45:13) deeplink to this comment

Please help me. When running below macro, I get the message "400". I don't know how to solution this problem?

Comment by: Jan Karel Pieterse (27-3-2016 20:20:55) deeplink to this comment

Hi Hung,

You did not include your macro?

Comment by: Oliver Cortinas (14-6-2018 16:13:41) deeplink to this comment

Hi, i have that problem but the issue is different, i am running a macro on an excel file, the macro keeps running and then i try to open a new empty spreadsheet from excel, when the new file is open the macro that was running on the other file is closed, any idea why and if there is a solution to keep running macro even if someone open a new spreadshhet of excel?

Comment by: Jan Karel Pieterse (14-6-2018 16:22:42) deeplink to this comment

Hi Oliver,

Can you show the running code?

Comment by: Anne D Wilson (17-3-2020 11:56:00) deeplink to this comment

Wonderful! I had a macro which opened a worksheet and just stopped. I wasted many hours trying to identify the cause before reading this article. I put a DoEvents statement before the instruction and everything was OK. Thankyou!

i = DoEvents
Workbooks.Open Filename:=wrkbknminc

Comment by: Paul Vedamuttu (1-6-2020 20:47:00) deeplink to this comment

Same for me; ...Open Filename:= "WB.xlsx" worked one day and stopped the next ! Just added i = DoEvents and functions resumed. Weird.

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.