Error Closing Userform

[sassriverrat]

I'm popping an error when I hit the "X" closing a userform. Here's the code that should close it. Am I missing something obvious?

thanks!

Private Sub Userform_QueryClose(cancel As Integer, closemode As Integer)On Error GoTo Helper
If closemode = 0 Then
    'Application Closer
    If Workbooks.Count > 1 Then
        ActiveWorkbook.Close
    Else: Application.Quit
    End If
End If
'Error Clearing Code
Exit Sub
Helper:
    resp = MsgBox("We're sorry to see you've encountered an error." & vbCrLf & vbCrLf & "To proceed, we recommend you contact the Developer " & _
    "with error codes [1026] and " & "[" & Err.Number & "-" & Err.Description & "]." & vbCrLf & vbCrLf & "To attempt to patch your problem at least " & _
    "temporarily, we recommend you click [Yes] to see help directions. Would you like to continue?", vbYesNoCancel, name)
        If resp = vbYes Then
            Call Error_Handle(sProcName, Err.Number, Err.Description)
        ElseIf resp = vbNo Then
            Exit Sub
        ElseIf resp = vbCancel Then
            Exit Sub
        End If
End Sub

 

[jasonb75]

On Error needs to be on a separate line, but I'm guessing that is just a formatting error when you copied the code to the forum.

What is the error message?

Which line does the error occur on ?

I just ran a quick test, there is nothing in the code that should cause an error anyway. The only thing that comes to mind is something is preventing it from closing the workbook.

 

[sassriverrat]

Yes that was just a formatting issue.

The error is a 424 object required which is what confuses me.

 

[sassriverrat]

Just had an idea- I have a short piece of code in the workbook section for auto saving when the workbook is closed. Could that pop it?

 

[jasonb75]

Possibly, have you tried debugging to see where the error actually occurs?

A bit less brute force by closing the workbook instead of quitting the application when there is only 1 open workbook might help.

 

[sassriverrat]

So two interesting things came up:

1. If another workbook is open, it doesn't have this problem. However, it does ask me if I want to save changes (it's not supposed to ask me (see below code).

2. When compiling project in debug, I have this small section of code on every part of the project, yet this workbook has about 260 "parts" and this section error'd only twice, both places saying "resp" wasn't defined.

On Error Goto Helper   (code)  Exit SubHelper:
    resp = MsgBox("We're sorry to see you've encountered an error." & vbCrLf & vbCrLf & "To proceed, we recommend you contact the Developer " & _
    "with error codes [1144] and " & "[" & Err.Number & "-" & Err.Description & "]." & vbCrLf & vbCrLf & "To attempt to patch your problem at least " & _
    "temporarily, we recommend you click [Yes] to see help directions. Would you like to continue?", vbYesNoCancel, name)
        If resp = vbYes Then
            Call Error_Handle(sProcName, Err.Number, Err.Description)
        ElseIf resp = vbNo Then
            Exit Sub
        ElseIf resp = vbCancel Then
            Exit Sub
        End If

Here is the closing code in ThisWorkbook
If another workbook is open, it asks me if I want to save changes in "Master Voyage Report" which is shouldn't do.

Private Sub Workbook_BeforeClose(cancel As Boolean)
On Error GoTo Helper
'This is the last event to run as Excel is closing


Sheets("Notes").Visible = xlVeryHidden
Sheets("Developer").Visible = xlVeryHidden




'Kills Noon/Arrival Sheets to Preserve Template
If ActiveWorkbook.name = "Master Voyage Report.xlsm" Then
        Application.DisplayAlerts = False
        Sheets("Developer").Range("A2:D3,A5:D5,A7:D7").ClearContents
    For Each sh In ThisWorkbook.Worksheets
        If LCase(sh.name) Like "noon*" Then
            If Len(sh.name) > 4 Then
                If IsNumeric(Mid(sh.name, 5, Len(sh.name))) Then sh.Delete
            Else
                sh.Delete
            End If
        End If
    Next
    If SheetExists("Arrival") Then
        Sheets("Arrival").Delete
    End If
    If SheetExists("Voyage Specifics") Then
        Sheets("Voyage Specifics").Delete
    End If
Else: Sheets("Ports").Visible = xlVeryHidden
End If


Application.DisplayAlerts = True


ActiveWorkbook.Save


'Error Clearing Code
Exit Sub
Helper:
    resp = MsgBox("We're sorry to see you've encountered an error." & vbCrLf & vbCrLf & "To proceed, we recommend you contact the Developer " & _
    "with error codes [1014] and " & "[" & Err.Number & "-" & Err.Description & "]." & vbCrLf & vbCrLf & "To attempt to patch your problem at least " & _
    "temporarily, we recommend you click [Yes] to see help directions. Would you like to continue?", vbYesNoCancel, name)
        If resp = vbYes Then
            Call Error_Handle(sProcName, Err.Number, Err.Description)
        ElseIf resp = vbNo Then
            Exit Sub
        ElseIf resp = vbCancel Then
            Exit Sub
        End If




End Sub

 

[jasonb75]

Q1. The unexpected Save prompt could be down to order of events if things are done slightly differently depending on the workbooks that are open.
If you are attempting to close the workbook from a procedure that has EnableEvents set to False, then the Before_Close event will not fire. You would need to set events back to True before attempting to close the workbook.

Also, the alert suppression is after the workbook name check, so any copies with different names will still promt to save.

Q2 from above, "resp" not defined means that you have Option Explicit at the top of the module to trap undeclared variables, but "resp" has not been declared in that procedure. (Dim resp As Long).

 

[sassriverrat]

Ok so going through and cleaning some stuff up (thank you very much for the help by the way!!!), I've run into a formula issue in VBA.

I have a function which references the previous sheet (called "prevsheet"). So in a cell, typing "=Prevsheet(A1)" references cell "A1" on the previous sheet. Anyway, works perfectly but within VBA, when compiling, "Prevsheet" as seen below is highlighted and it says argument not optional.

Dim Form1 As DoubleDim Form2 As Double
Dim ws As Worksheet


Form1 = FormulaR1C1 = Sheets("Voyage Specifics").Range("D8")
Form2 = FormulaR1C1 = PrevSheet.Range("D8")
Set ws = ActiveSheet.Previous


If ws.name Like "Noon*" Then
    ActiveSheet.Range("D9") = Form2
Else: ActiveSheet.Range("D9") = Form1
End If

 

[jasonb75]

PrevSheet is a function not a command, so it needs to be entered properly, with the argument in parentheses.

From your code it is unclear if you're trying to enter the formula into the sheet, or just evaluate it in vba.

This might work, but I think that it could be a recipe for disaster.

Form2 =[PrevSheet(D8)]

 

[sassriverrat]

It is intended that I am entering prevsheet into vba to be entered as a formula into a sheet. (Basically the user hits a “button” which runs code to create a sheet, format it, and input appropriate cells with various formulas for population)