Userform Closing Excel

[sassriverrat]

Hello,

I have a userform that has decided that it is going to close excel each time I use it. I'm not sure where I've gone wrong in my coding and was hoping someone could double check it for me.

thanks!

 Private Sub CommandButton5_Click()'"next" page button
On Error GoTo Helper
'Application.Visible = False
    Unload Me
    UserForm2.Show


'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 [1044] 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
            UserForm18.Show
            'MsgBox ("Success")
        ElseIf resp = vbNo Then
            Exit Sub
        ElseIf resp = vbCancel Then
            Exit Sub
        End If
End Sub

Private Sub UserForm_Terminate()
On Error GoTo Helper
Unload Me
            'Application Closer
                If Workbooks.Count > 1 Then
                    ActiveWorkbook.Close
                Else: Application.Quit
                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 [1060] 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
            UserForm18.Show
            'MsgBox ("Success")
        ElseIf resp = vbNo Then
            Exit Sub
        ElseIf resp = vbCancel Then
            Exit Sub
        End If
End Sub

 

[RoryA]

Yes you can.

 

[sassriverrat]

How would I go about doing it? My thought being that I have a TON of modules and would love if on error, userform18 would come up with the error code...then I'd put code in (that i know) that would allow a user to send an email directly to me with the error code displayed in the userform....

that and code to screenshot the line of the error would be awesome

Thanks!

 

[RoryA]

You haven't really given a lot of information, but assuming you have a form with textbox1 to display the error, you'd add an error handler to each routine and call a routine that does something like this:

With userformname
.textboxname.text = err.description
.show
end with

 

[sassriverrat]

Oh fair enough. Ok, so I just snagged a piece of code out of a randomly chosen module. Obviously this is at the end of the module (just a snippet) and the module starts with "On Error Goto Helper."

'Error Clearing CodeExit 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 [1105] 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
            UserForm18.Show
            'MsgBox ("Success")
        ElseIf resp = vbNo Then
            Exit Sub
        ElseIf resp = vbCancel Then
            Exit Sub
        End If
End Sub

Every module has a unique identifier (in this case 1105) that I've assigned so that I can, when told there's an error, I can find which module/section of code to start in (now you see there's a LOT of pieces to this workbook....).

Further, if the user hits "Yes", the idea is to have Userform18 show. Userform18 has a few cmd buttons and a text box (for displaying the error). One cmd is to return to the workbook with the error reset (i.e. just skipping over it), one is to have a printout of the error (not sure how to do this yet), and one is to send the email (knwo how to send the email) with the error (not sure how) and a screenshot of the error (showing the line) (not sure how to do)...

thanks for your help!

 

[sassriverrat]

This is amazing @RoryA. Took me a minute to actually realize how it was working and then I was like "Duh, brilliant!"

So one last question- based on the code below, and the userform18 code posted below (which as you can see, there isn't much...), and the "email" coding posted below (for sending an email)- anyway- trying to send a debug.print (of an error code/error'd piece) screen in that email. Would you know how to do this?

thanks again @RoryA, really!

new error coding

 'Error Clearing CodeExit 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 [1011] 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
            With UserForm18
                .TextBox1.Text = Err.Number & "-" & Err.Description
                .Show
            End With
        ElseIf resp = vbNo Then
            Exit Sub
        ElseIf resp = vbCancel Then
            Exit Sub
        End If
    
End Sub

userform18 (the error userform)

Private Sub CommandButton1_Click()'Email Developer


End Sub


Private Sub CommandButton2_Click()
'Print Error Stuff


'some sort of printout of the debug screen for the appropriate error- not sure on this yet


End Sub


Private Sub CommandButton3_Click()


'Close userform


End Sub


Private Sub Label2_Click()


End Sub


Private Sub TextBox1_Change()
'error code and description
'self populating through error code- leave blank
End Sub


Private Sub Userform_Initialize()
On Error GoTo Helper
Caption = ThisWorkbook.Sheets("Notes").Range("N4")
Label3 = "Error Handling"
End Sub

email code

Sub Mail_Error()

'Begins Error Handling Code
On Error GoTo Helper


    Dim wb As Workbook
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim OutApp As Object
    Dim OutMail As Object
    Dim emailaddress As String


    
    If TempFilePath = Environ$("temp") & "\" Then
          FileExtStr = ".xlsm": FileFormatNum = 52
    End If
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Set OutApp = CreateObject("Outlook.Application")
     With ThisWorkbook.ActiveSheet
        If Sheets("Notes").Range("L34").Value Like "?*@?*.?*" Then
            emailaddress = Sheets("Notes").Range("L34").Value
            TempFileName = TempFilePath & .name & " " & ThisWorkbook.name & " " & Format(Now, "dd-mmm-yy h-mm-ss") & ".pdf"
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=TempFileName, _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .to = emailaddress
                .CC = ""
                .BCC = ""
                .Subject = "Engine Quote"
                .Body = err.number & "-" & err.description 
'would this work? Can I add a debug piece here?

                .Attachments.Add TempFileName
                .Send
            End With
            On Error GoTo 0
            Set OutMail = Nothing
            Kill TempFileName
        End If
    End With
    Set OutApp = Nothing
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
'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 [1113] 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
            UserForm18.Show
            'MsgBox ("Success")
        ElseIf resp = vbNo Then
            Exit Sub
        ElseIf resp = vbCancel Then
            Exit Sub
        End If
End Sub