Subscribe in a readerMacro 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 27 in total (Show All Comments):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:
Application.Ontime Now, "ContinueOpen"
End Sub
Then in a normal module:
'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.
Comment by: Larry (7/23/2012 11:17:17 PM)I tried the following code in my macro:
Application.OnTime (Now,"MyWorkbookOpen")
and I got the following error:
"Compile Error: Syntax Error"
I have tried modifying this code in many ways, including syntax similar to this:
Application.OnTime When:="15:55:00", Name:="Macro1"
which I found on a Microsoft website.
I am using Excel 2003. Any suggestions are appreciated!
Comment by: Jan Karel Pieterse (8/7/2012 11:30:16 AM)Hi Larry,
The syntax is:
Workbooks.Open Now(), "MyWorkbookOpen"
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.