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

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

Comment by: Jan Karel Pieterse (6/18/2011 11:10:09 AM)

Can you perhaps show the code of the second workbook that is supposed to open the third?

Comment by: Roy Clark (6/18/2011 10:40:28 PM)

I completely reconstructed a new workbook, and the problem seems to have gone away for now. I am finding that the procedures in Open_Workbook are fickle, as I do not understand the timing of events, and so we end up easily crashing the program in this procedure (calling events that may not be open yet, or hiding sheets or books that are closing/opening, asking for range names that are not yet registered? etc?), and just one crash seems to permanently disable activeX controls?

Really appreciate your input.Here are copies of two subs, both in the second workbook. The second is in a module.

Private Sub Workbook_Open()
Application.ScreenUpdating = False

'We then mine the Structural Steel workbook path/name from the Global iEngine names
Dim VariableValue As String
VariableValue = GetHiddenNameValue("StructuralSteelModule")

'We now open the workbook listed above
Call OpenTheWorkbookR0003(VariableValue)

'ActiveWindow.Visible = False ' I had to disable this as it will always crash the program!
Application.ScreenUpdating = True
Application.CalculateFull
Application.Calculation = xlCalculationAutomatic

End Sub

The OpenTheWorkbook sub looks like the following:

Public Sub OpenTheWorkbookR0003(FileName1 As String)
Application.ScreenUpdating = False
Dim FilePath1 As String
Dim FullFilePath1 As String
Dim WBook As Workbook

FilePath1 = "C:\Documents and Settings\Owner\Desktop\iEngine\"
On Error Resume Next
'Check to see if file already open
Set WBook = Workbooks(FileName1)
     If WBook Is Nothing Then 'Not open
     Else: Exit Sub
    End If
FullFilePath1 = FilePath1 & FileName1 ' this is for the selected worksheet file path

Err.Clear 'Clear erroneous errors

Set WBook = Workbooks.Open(FullFilePath1, False, False) ' Update links?, Read only?
Application.ScreenUpdating = True

End Sub

Comment by: Jan Karel Pieterse (6/19/2011 11:30:11 PM)

Hi Roy,

It is a known problem that Excel may not have finished doing all of its chores when the Workbook_Open event fires, leading to unexpected errors in your code. A workaround for this is to move all of the code *inside* the open event to a separate sub in a normal module.
Then in the Workbook_Open event, you use the Application.Ontime method to call the remainder fo the code you want executed when the file was opened. This gives Excel the opportunity to finish everything it needs to do during startup:

Private Sub Workbook_Open()
Application.Ontime Now, "ContinueOpen"
End Sub

Then in a normal module:

Sub ContinueOpen()
'Real workbook_Open code goes here
End Sub

Comment by: Shivam Srivastava (9/22/2011 4:07:07 AM)

I am running a code which is to create hyperlinks on selected cells.The problem is when the destination worksheet is opened and the macro is running for the first time on the fresh destination sheet it does not link but when the macro is rum for the second time on wards the linking is perfect.I can not understand the reason, I want it to run in the first time perfectly.

Comment by: Jan Karel Pieterse (9/22/2011 7:46:36 AM)

Hi Shivam,

Maybe the trick I showed Roy (just above your message) helps for your problem too?

Comment by: Vince (11/1/2011 4:12:50 PM)

When I open excel from the icon, it opens OK but then launches VBA, how can I stop this

Comment by: Jan Karel Pieterse (11/2/2011 8:02:58 AM)

Hi Vince,

Maybe this is caused by an Add-in or something like it?
check out:
www.jkp-ads.com/articles/startupproblems.asp

Comment by: Jim (1/24/2012 4:19:22 PM)

Thanks a ton, Jan! Your solution worked great for my problem that was driving me nuts.

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.