VBA (excel) quits on workbooks.open

[Nicholas Dreyer]

I have an ellaborate VBA project that does of most of any needed
openening and closing of workbooks. I am trying to make it so
everything remains under control when a user opens and closes
workbooks directly from excel.

Anyway, the details are to involved to get into now, but here is a
very strange symptom that happens if a workbook is closed directly
from excel and then opened from code within VBA:

Any statements after

workbooks.open <nameofworkbook_just_closed>

never are executed.

What is particularly difficult in trying to debug this is that when
you step through the code using F8 key, the code does *not* quit on
the workbook.open, but continues to completion as intended.

Anybody ever seen this symptom and know what possible causes there
are? No need to suggest using On Error Goto 0. That has already been
done . . .

Thanks a-million for any suggestions.

Nick

 

[Peter T]

Hi Nick,

With this simple test I couldn't replicate your problem

Dim msName$, msFullname$

Sub StoreFileName()
'ActiveWorkbook is a previously saved wb
msFullname = ActiveWorkbook.FullName
msName = ActiveWorkbook.Name

End Sub

' manually close the activeworkbook

Sub OpenLastFile()

'Workbooks(msName).Close False
Workbooks.Open msFullname

MsgBox ActiveWorkbook.Name
End Sub

OpenLastFile runs to the end, whether the file had been closed manually or
with the close line uncommented.

Code that just suddenly stops with no trappable error can be due to the
project recompiling for some reason. Code can also just stop due to an
unhandled error in a UDF particularly in xl97 but that's obviously not what
you have.

Regards,
Peter T

 

[Dave Peterson]

My guess is that you're using a shortcut key to start your macro. And that
shortcut key includes a shift key.

Remove the shift key from that shortcut combination and try it again.

Holding the shiftkey when you open a workbook stops the open macros from
running. It also confuses excel/vba into thinking it should stop.

 

[Peter T]

Sounds very plausible, didn't think of that!
Also a previously held Shift can become 'sticky' with similar effect

Regards,
Peter T

 

[Nicholas Dreyer]

True, I am starting a variety of macros with key-strokes setup via

application.onkey <ctrl><shift><some_key>

including the offending one.

Am not at work now to test, but I'll remove the shift sequence
tomorrow and report back if that resolves the problem.

Thanks for all of your help

Nick

Sounds very plausible, didn't think of that!
Also a previously held Shift can become 'sticky' with similar effect

Regards,
Peter T

 

[Nicholas Dreyer]

Thanks a-million to both Dave and Peter for your helpful replies.
You have pinpointed the exact cause of my grief.

In case you are not aware of the trick below, I am including code to
show a workaround to the problem which will work if user warnings are
desired/aceptable every time a workbook needs to be opened.

Going through a userform before the open statement appears to make the
problem go away.

Since I was working in an environment where <ctrl><shift><Key> has
become established for navigating around excel via macros that include
some workbook file management, I was glad to discover this.

Before workbooks.open I had always included something like

msgbox "I am about to open : & <filename>

Replacing this with code that loads a simple userform with one command
button captioned "Ok" to unload the form makes al the difference.

Put the following code in a module of a project that contains a
userform "UserForm1"and label "Label1":
___________________________________________

Option Explicit
Sub SetKeys()
Application.OnKey "^+c", "CrashonOpen"
Application.OnKey "^+g", "GetpastOpen"
End Sub

Sub CrashonOpen()
MsgBox "I am about to open MyBook"
Workbooks.Open "MyBook.xls"
MsgBox "I would like to get here"
End Sub

Sub GetpastOpen()
With UserForm1
.Label1 = "I am about to open MyBook"
.Show
End With
Workbooks.Open "MyBook.xls"
MsgBox "I have gotten here"
End Sub
___________________________________________


|\|.

Sounds very plausible, didn't think of that!
Also a previously held Shift can become 'sticky' with similar effect

Regards,
Peter T

 

[Nicholas Dreyer]

Thanks a-million to both Dave and Peter for your helpful replies.
You have pinpointed the exact cause of my grief.

In case you are not aware of the trick below, I am including code to
show a workaround to the problem which will work if user warnings are
desired/aceptable every time a workbook needs to be opened.

Going through a userform before the open statement appears to make the
problem go away.

Since I was working in an environment where <ctrl><shift><Key> has
become established for navigating around excel via macros that include
some workbook file management, I was glad to discover this.

Before workbooks.open I had always included something like

msgbox "I am about to open : & <filename>

Replacing this with code that loads a simple userform with one command
button captioned "Ok" to unload the form makes al the difference.

Put the following code in a module of a project that contains a
userform "UserForm1"and label "Label1":
___________________________________________

Option Explicit
Sub SetKeys()
Application.OnKey "^+c", "CrashonOpen"
Application.OnKey "^+g", "GetpastOpen"
End Sub

Sub CrashonOpen()
MsgBox "I am about to open MyBook"
Workbooks.Open "MyBook.xls"
MsgBox "I would like to get here"
End Sub

Sub GetpastOpen()
With UserForm1
.Label1 = "I am about to open MyBook"
.Show
End With
Workbooks.Open "MyBook.xls"
MsgBox "I have gotten here"
End Sub
___________________________________________


|\|.

Sounds very plausible, didn't think of that!
Also a previously held Shift can become 'sticky' with similar effect

Regards,
Peter T

 

[Peter T]

Another mystery solved !

There's still a possibility user could leave a coffee cup on the shift key.
I know unlikely but here's another way to wait until shift is released or
nudge user every second or give up. Typically though user would release
shift within half a second and not need to see and dismiss a message or
userform. (Further comment below)


Public Declare Function GetAsyncKeyState _
Lib "user32" (ByVal vKey As Long) As Integer
Public Declare Function GetTickCount Lib "kernel32.dll" () As Long

Sub SetOnkey()
Application.OnKey "+T", "TestShift"

' uncomment to clear the shortcut after testing
'Application.OnKey "+T", ""
End Sub

Sub TestShift()
' run from Excel with Shift-T and keep shift held
' while dismissing the msgbox in ShiftStillOn

If ShiftStillOn("TestShift") Then
MsgBox "Give up", vbExclamation, "Shift still down"
Exit Sub
End If

MsgBox "Ready to do stuff", , "Shift off"

End Sub

Function ShiftStillOn(Optional sMsg As String) As Boolean
Dim bShiftOn As Boolean
Dim nTick As Long
Dim i As Long

nTick = GetTickCount

If Len(sMsg) Then
sMsg = sMsg & vbCr
End If
sMsg = sMsg & "Let go of Shift"

bShiftOn = True

Do While bShiftOn

Do While GetTickCount < (nTick + 1000) And bShiftOn
bShiftOn = IsKeyDown(vbKeyShift)
Loop

i = i + 1
If i = 4 Then Exit Do ' avoid endless loop

If bShiftOn Then MsgBox sMsg, , "nudge " & i
nTick = GetTickCount

Loop

ShiftStillOn = bShiftOn

End Function

Function IsKeyDown(key As Long) As Boolean
If GetAsyncKeyState(key) Then
IsKeyDown = True
End If
End Function


For my purposes I need to check if shift is held when user starts my app
from a menu button in order to do something slightly differently. I use
IsKeyDown as above but occasionally got a false positive. I can only put
this down to sticky shift syndrome as I mentioned earlier, Not sure if this
occurs in all versions Windows & Excel.

Whatever, I've found running the function twice appears to clear the 'false'
buffer and return the true state of Shift.

Regards,
Peter T

Thanks a-million to both Dave and Peter for your helpful replies.
You have pinpointed the exact cause of my grief.

In case you are not aware of the trick below, I am including code to
show a workaround to the problem which will work if user warnings are
desired/aceptable every time a workbook needs to be opened.

Going through a userform before the open statement appears to make the
problem go away.

Since I was working in an environment where <ctrl><shift><Key> has
become established for navigating around excel via macros that include
some workbook file management, I was glad to discover this.

Before workbooks.open I had always included something like

msgbox "I am about to open : & <filename>

Replacing this with code that loads a simple userform with one command
button captioned "Ok" to unload the form makes al the difference.

Put the following code in a module of a project that contains a
userform "UserForm1"and label "Label1":
___________________________________________

Option Explicit
Sub SetKeys()
Application.OnKey "^+c", "CrashonOpen"
Application.OnKey "^+g", "GetpastOpen"
End Sub

Sub CrashonOpen()
MsgBox "I am about to open MyBook"
Workbooks.Open "MyBook.xls"
MsgBox "I would like to get here"
End Sub

Sub GetpastOpen()
With UserForm1
.Label1 = "I am about to open MyBook"
.Show
End With
Workbooks.Open "MyBook.xls"
MsgBox "I have gotten here"
End Sub
___________________________________________


|\|.