VBA (excel) quits on workbooks.open
Nicholas Dreyer
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
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
"Nicholas Dreyer" <gur...@oz.net> wrote in message
news:45499387...@news.earthlink.net...
Dave Peterson
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.
--
Dave Peterson
Peter T
Also a previously held Shift can become 'sticky' with similar effect
Regards,
Peter T
"Dave Peterson" <pete...@verizonXSPAM.net> wrote in message
news:454A0555...@verizonXSPAM.net...
Nicholas Dreyer
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
On Thu, 2 Nov 2006 15:13:36 -0000, "Peter T" <peter_t@discussions>
wrote:
Nicholas Dreyer
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
___________________________________________
|\|.
On Thu, 2 Nov 2006 15:13:36 -0000, "Peter T" <peter_t@discussions>
wrote:
>Sounds very plausible, didn't think of that!
Nicholas Dreyer
|\|.
>Sounds very plausible, didn't think of that!
Peter T
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
"Nicholas Dreyer" <gur...@oz.net> wrote in message
news:454d0ab...@news.earthlink.net...