Closing Errors...

[thomaspatton]

Hopefully this'll be the last time I need help from ya'll for awhile. My workbooks almost done. Unformtunately, I'm running into an application problem now. Something in my VBA code is causing an App Error, not a Runtime error.

Here's my setup. User have a Right Click Menu that controls everything, including saving and exiting the Workbook. I don't give them access to the File menu or the X Button.

My Save code, placed in a module:
[vba]Public Sub AutoSv()
Dim OrgDir As String
Dim NwFy As String
Dim NwClsDt As String
If Worksheets("Class Information").Range("j1").Value = "" Then
MsgBox "You must first enter a Class Number in Cell J1 of the Class Information tab before saving.", vbOKOnly, "Stop!"
Exit Sub
Else
NwFy = "FY " & Right(Worksheets("Class Information").Range("j1").Value, 2)
NwClsDt = Worksheets("Class Information").Range("j1").Value
OrgDir = Left$(ActiveWorkbook.Path, 2) & "\amo\student information\" & NwFy & "\Class " & NwClsDt & "\"
ActiveWorkbook.SaveAs Filename:=OrgDir & "AMO-" & Worksheets("Class Information").Range("j1").Value & ".xls"
End If
End Sub[/vba]

My closing code, attached to a UF Button:
[vba]Private Sub cmdExtWk_Click()
Dim ExStr As String
Dim ClsMsg As String
ufCommon.Hide
ExStr = MsgBox("Are you sure you want to exit the Student Workbook?", vbYesNo, "Closing Workbook...")
Select Case ExStr
Case Is = vbYes
swtchUpdateOff
With Application
.CellDragAndDrop = True
.OnKey "^{p}"
.OnKey "^{s}"
.OnKey "%{f11}"
.OnKey "^{c}"
.OnKey "^{v}"
.OnKey "^{a}"
.OnKey "^%{t}"
.CommandBars("Worksheet Menu Bar").Enabled = True
.CommandBars.ActiveMenuBar.Enabled = True
.DisplayFullScreen = False
.Worksheets("Security Warning").Visible = True
.Worksheets("Security Warning").Activate
swtchUpdateOn
End With
ClsMsg = MsgBox("Do you want to save changes?", vbYesNo, "Closing Workbook...")
Select Case ClsMsg
Case Is = vbYes
Application.Quit
ActiveWorkbook.Close True
Case Is = vbNo
Application.Quit
ActiveWorkbook.Close False
End Select
Case Is = vbNo
ufCommon.Show
End Select
End Sub[/vba]

I've got a couple other procedures attached to the save portion that call it, but none of them relate to closing the book.

Sorry I can't give more info on this, but when the App errors out, I submit it to MS but don't get the "See how to fix this" link.

I uploaded my book in case neither of these snippets is the actual culprit.
*edit* See below

[Bob Phillips]

Try closing te workbook before you quit.

[thomaspatton]

Nope. I switched the lines and it still errors out with...
[vba] Case Is = vbNo
ActiveWorkbook.Close False
Application.Quit[/vba]

[tpoynton]

I dont know if it is related to the error, but it seems like you are quitting the application before you are done with the userform. I'm hesitant to open the workbook because I dont want my commandbars fiddled with!

[mdmackillop]

Use OnTime to close the application
[VBA]
Sub Test()
ClsMsg = MsgBox("Do you want to save changes?", vbYesNo, "Closing Workbook...")
Select Case ClsMsg
Case Is = vbYes

Application.OnTime Now + TimeValue("00:00:05"), "personal.xls!module1.my_Procedure"
ActiveWorkbook.Close True

Case Is = vbNo
Application.OnTime Now + TimeValue("00:00:05"), "personal.xls!module1.my_Procedure"
ActiveWorkbook.Close False
End Select

End Sub

'Place in appropriate module
Sub my_Procedure()
Application.Quit
End Sub

[/VBA]

[thomaspatton]

So the "+TimeValue(),Procedure" is just basically giving the App more time to close the worksheet before it initiates closing? Genius!

Before I throw this in here, lemme confirm that I need to change "Personal.xls" to just the workbook name and change "module1." to the actual module name I place the "my_Procedure" in?

[Bob Phillips]

No it is not more time, if that were so a simple wait works. The ontime allows things to disconnect m ore thoroughly before trying to initiate another action. It is a bug (or many bugs) in my view, but this workaround works in many instances.

[thomaspatton]

Ok, I tried adding the .OnTime's but it's still erroring out. Here's what I did
[vba]Private Sub cmdExtWk_Click()
Dim ExStr As String
Dim WkNm As String
ufCommon.Hide
ExStr = MsgBox("Are you sure you want to exit the Student Workbook?", vbYesNo, "Closing Workbook...")
WkNm = ActiveWorkbook.Name
Select Case ExStr
Case Is = vbYes
Dim ClsMsg As String
On Error Resume Next
swtchUpdateOff
With Application
'.CellDragAndDrop = True
'.OnKey "^{p}"
'.OnKey "^{s}"
'.OnKey "%{f11}"
'.OnKey "^{c}"
'.OnKey "^{v}"
'.OnKey "^{a}"
'.OnKey "^%{t}"
'.OnKey "%{f4}"
'.CommandBars("Worksheet Menu Bar").Enabled = True
'.CommandBars.ActiveMenuBar.Enabled = True
'.CommandBars("Formula Bar").Enabled = True
'.DisplayFullScreen = False
.Worksheets("Security Warning").Visible = True
.Worksheets("Security Warning").Activate
swtchUpdateOn
End With
ClsMsg = MsgBox("Do you want to save changes?", vbYesNo, "Closing Workbook...")
Select Case ClsMsg
Case Is = vbYes
Application.OnTime Now + TimeValue("00:00:05"), "WkNm!modCommons.Qt"
ActiveWorkbook.Close True
Case Is = vbNo
Application.OnTime Now + TimeValue("00:00:05"), "WkNm!modCommons.Qt"
ActiveWorkbook.Close False
End Select
Case Is = vbNo
ufCommon.Show
End Select
End Sub[/vba]

WkNm is Dim'ed at the beginning to set ActiveWorkbook.Name
Qt is a sub in thr module name modCommons

Also, since Tpoynton expressed distaste with my dummy method of preventing faulty user access, I commented out all my Tollbar removal code and added a Proc called "EmergencyRestore" in acse you run into problem, and reuploaded it here :

http://h1.ripway.com/thomaspatton/St...kbookv3.27.rar

[mdmackillop]

I don't believe the application can access the code once the workbook is closed, which is why I placed it in Personal.xls. This will remain open until the Application quits.

[mdmackillop]

Not thinking clearly today!
Why not simply
[VBA]
Sub Test()
ClsMsg = MsgBox("Do you want to save changes?", vbYesNo, "Closing Workbook...")
Select Case ClsMsg
Case Is = vbYes
ActiveWorkbook.Close True
Case Is = vbNo
ActiveWorkbook.Close False
End Select
End Sub

'In ThisWorkbook module
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Quit
End Sub

[/VBA]

[thomaspatton]

Not thinking clearly today!
Why not simply...

Crapsicles! Cause that doesn't work either. I also went back to the .TimeValue method using the format you had (basically just copying and pasting) and it still errored out.

This is a wierd kind of error, because the workbook closes out and then I'm getting the App Error...

[thomaspatton]

Any other ideas on this one, or we just callin it unsolveable?

[mdmackillop]

Can you post a sanitised copy of your workbook? I can't replicate your problem.

[thomaspatton]

What ya mean by "sanitized"? Last one I uploaded in post 8 had the Toolbar and Fullscreen stuff removed.

[tpoynton]

OK, tinkered around A LOT, and it seems to be a combination of things...

right-clicking to show the ufcommon form seems to be part of the problem; if you put ufcommon.show into the immediate window and click on exit, it works...but then, you'll see what might be the rest of the problem - it appears to be looking for things that dont exist (wknum.xls and wknum.htm, if I recall).

i have a workbook with a ton of changes I made, but i think that will get you headed in the right direction...perhaps. I can send the workbook with what i have if you like.