Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Macro in Excel Stops After A Workbook.Open Command

Macro in Excel Stops After A Workbook.Open Command

Author: Jan Karel Pieterse MVP

View products that this article applies to.

Symptoms

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.

↑ Back to the top

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.

↑ Back to the top

Resolution

The workaround for this problem (only applicable on Windows � platforms) is to detect whether the shift key is pressed and wait for it to be released before issuing the Workbooks.Open command:
�
'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 ="C:\My Documents\ShiftKeyDemo.xls"
End Sub
���

↑ Back to the top

Properties

COMMUNITY SOLUTIONS CONTENT DISCLAIMER
MICROSOFT CORPORATION AND/OR ITS RESPECTIVE SUPPLIERS MAKE NO REPRESENTATIONS ABOUT THE SUITABILITY, RELIABILITY, OR ACCURACY OF THE INFORMATION AND RELATED GRAPHICS CONTAINED HEREIN. ALL SUCH INFORMATION AND RELATED GRAPHICS ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT AND/OR ITS RESPECTIVE SUPPLIERS HEREBY DISCLAIM ALL WARRANTIES AND CONDITIONS WITH REGARD TO THIS INFORMATION AND RELATED GRAPHICS, INCLUDING ALL IMPLIED WARRANTIES AND CONDITIONS OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, WORKMANLIKE EFFORT, TITLE AND NON-INFRINGEMENT. YOU SPECIFICALLY AGREE THAT IN NO EVENT SHALL MICROSOFT AND/OR ITS SUPPLIERS BE LIABLE FOR ANY DIRECT, INDIRECT, PUNITIVE, INCIDENTAL, SPECIAL, CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER INCLUDING, WITHOUT LIMITATION, DAMAGES FOR LOSS OF USE, DATA OR PROFITS, ARISING OUT OF OR IN ANY WAY CONNECTED WITH THE USE OF OR INABILITY TO USE THE INFORMATION AND RELATED GRAPHICS CONTAINED HEREIN, WHETHER BASED ON CONTRACT, TORT, NEGLIGENCE, STRICT LIABILITY OR OTHERWISE, EVEN IF MICROSOFT OR ANY OF ITS SUPPLIERS HAS BEEN ADVISED OF THE POSSIBILITY OF DAMAGES.

↑ Back to the top

Community solutions content disclaimer

Microsoft corporation and/or its respective suppliers make no representations about the suitability, reliability, or accuracy of the information and related graphics contained herein. All such information and related graphics are provided "as is" without warranty of any kind. Microsoft and/or its respective suppliers hereby disclaim all warranties and conditions with regard to this information and related graphics, including all implied warranties and conditions of merchantability, fitness for a particular purpose, workmanlike effort, title and non-infringement. You specifically agree that in no event shall Microsoft and/or its suppliers be liable for any direct, indirect, punitive, incidental, special, consequential damages or any damages whatsoever including, without limitation, damages for loss of use, data or profits, arising out of or in any way connected with the use of or inability to use the information and related graphics contained herein, whether based on contract, tort, negligence, strict liability or otherwise, even if Microsoft or any of its suppliers has been advised of the possibility of damages.

↑ Back to the top

Keywords: KB555263, kbhowto, kbpubmvp, kbpubtypecca

↑ Back to the top

Article Info
Article ID : 555263
Revision : 1
Created on : 2/26/2005
Published on : 2/26/2005
Exists online : False
Views : 583