Userform is being sucked into vast nothingness.

[WrldIntlR]

This one's killing me ladies and gents.

I had this working fine for a while. I added error checking and now it's all messed up and I don't know why (other than my grasp of error control is tenuous at best).

Here's the situation: I have a userform, on activation, sets excel to application.visible = false. Therefore, if the userform errors out for any reason, I get an open instance of excel that I can't do anything about but log out and log back in.

Here's my submit code. When I get to the part about the "ALL YOUR DATA WILL BE LOST" Yes works fine. No closes the userform, when it should just exit the sub and allow the user to continue to input data.

Here's the code:

Private Sub cbClose_Click()
'On Error GoTo Errhandler
On Error GoTo Errhandler
If MsgBox("Attach and send as email?", vbInformation + vbYesNo) = vbYes Then
   'Variable declaration
    Dim oApp As Object, _
    oMail As Object, _
    WB As Workbook, _
    FileName As String, _
    UserName As String
    Dim irow As Long
    Dim lr As Long
    UserName = UserNameWindows()
 
     'Turn off screen updating
    Application.ScreenUpdating = False
 
     'Make a copy of the active sheet and save it to
     'a temporary file
    Sheets(Array("OCCData", "UPGData", "DISData")).Copy
    Set WB = ActiveWorkbook
    FileName = UserName & " " & Format(Now, "mmddyyyy") & ".xls"
    On Error Resume Next
    Kill "H:\" & FileName
    On Error GoTo 0
    WB.SaveAs FileName:="H:\" & FileName
 
     'Create and show the outlook mail item
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    With oMail
         .Subject = UserName & " " & Format(Now, "mmddyyyy") & " Tracking Results"
        .Attachments.Add WB.FullName
        .Display
    End With
 
     'Delete the temporary file
    WB.ChangeFileAccess Mode:=xlReadOnly
    Kill WB.FullName
    WB.Close savechanges:=False
 
     'Restore screen updating and release Outlook
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing
 
    Application.IgnoreRemoteRequests = False 'deactivate
    ThisWorkbook.Close savechanges:=False
    Application.Quit
    Unload Me
   Else
     If MsgBox("ALL OF YOUR RESULTS WILL BE LOST. Are you sure?", vbInformation + vbYesNo) = vbYes _
        Then
            ThisWorkbook.Close savechanges:=False
            Application.IgnoreRemoteRequests = False 'deactivate
            Application.Quit
            Unload Me
         Else
            Exit Sub
      End If
    End If
 
Exit Sub
 
Errhandler: GetMeOut
End Sub

Any help would be very much appreciated

 

[Warship]

I can't see anything obvious.
Try replacing:

If MsgBox("ALL OF YOUR RESULTS WILL BE LOST. Are you sure?", vbInformation + vbYesNo) = vbYes

With:

If MsgBox("ALL OF YOUR RESULTS WILL BE LOST. Are you sure?", vbInformation + vbYesNo) = 6

Only suggestion for now...

 

[Norie]

What do you mean an instance you can't do anything with?

Doesn't this code close the instance of Excel it's in?

 

[AlphaFrog]

Try something like this...

    Else
        If MsgBox("ALL OF YOUR RESULTS WILL BE LOST. Are you sure?", vbInformation + vbYesNo) = vbYes _
           Then
            ThisWorkbook.Close savechanges:=False
            Application.IgnoreRemoteRequests = False    'deactivate
            Application.Quit
            Unload Me
            Exit Sub
        End If
    End If
Errhandler:
GetMeOut

End Sub

 

[WrldIntlR]

Both good ideas, but no-gos . I cannot figure this bloody thing out.

 

[WrldIntlR]

What do you mean an instance you can't do anything with?

Doesn't this code close the instance of Excel it's in?

It does, when it wants to operate correctly. But if I get an error it shuts the userform down but excel is still floating in the ether. This wouldn't be a problem if it were just me, because I could kill it from tasks, but the intended user base will not have access to (or acumen for) task manager.

 

[Norie]

What error are you trying to handle anyway?

 

[WrldIntlR]

What error are you trying to handle anyway?

A "Since when am I a VBA programmer? I haven't written code since C+ in High School"-related error. I'm not very clear on error handling TBH. I have it in there because I got sick of me missing some reference somewhere or misspelling a name range and having to force-close the ghost instance and start up excel all over again. I was hoping if I caught an error it would gracefully end the program and restore Excel to full glory.

Sub GetMeOut()
MsgBox (Err.Number)
Application.IgnoreRemoteRequests = False 'deactivate
Application.Visible = True
Unload Me
End Sub

 

[WrldIntlR]

Try something like this...

    Else
        If MsgBox("ALL OF YOUR RESULTS WILL BE LOST. Are you sure?", vbInformation + vbYesNo) = vbYes _
           Then
            ThisWorkbook.Close savechanges:=False
            Application.IgnoreRemoteRequests = False    'deactivate
            Application.Quit
            Unload Me
            Exit Sub
        End If
    End If
Errhandler:
GetMeOut
 
End Sub

Interestingly, this does do the job of quitting excel entirely and not leaving the ghost instance open, if that means anything to you (normally when it got to this point it would just close the userform and leave excel running invisible. Now excel is actually closing). Still not what I need it to do, though. I need it to just return to the userform as if nothing had happened.

 

[Norie]

Error handling is not going to help you with those sort of errors, it might not even catch them.

If you want to catch misspelled range names add Option Explicit at the top of the module.

Can you explain exactly what you want to happen?

You seemed to say in the original post you wanted the userform closed, but now you say you want to return to the userform?