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

 

[WrldIntlR]

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?

Sorry. I haven't eaten food in a few hours.

This is the deal. I have a button that copies the worksheets I need to it's own new workbook, attaches that workbook to an email to be sent, and then closes the workbook they're working out of and the application instances without saving any changes.

I want to my users absolutely sure they understand that by exiting without emailing (going through that previous process), they are losing all of the information they just inputted. The last message box is supposed to achieve that end (i.e. they click no, and the whole submission process ends without the userform closing)

It's key that none of their changes get saved to the workbook they're working out of.

 

[Norie]

Have you tried removing the On Error stuff?

It could actually be hiding errors, and if it is that could be why an instance of Excel persists.

Also you error handling isn't actually going to close Excel or any workbook.

It just seems to jump immediately to the end of the code.

You could probably fix that by adding Application.Quit etc just before the sub ends.

 

[WrldIntlR]

Have you tried removing the On Error stuff?

It could actually be hiding errors, and if it is that could be why an instance of Excel persists.

Also you error handling isn't actually going to close Excel or any workbook.

It just seems to jump immediately to the end of the code.

You could probably fix that by adding Application.Quit etc just before the sub ends.

I could have sworn I had tried that already, but apparently not becuase once I removed the onerror crap it works perfectly now.

Thanks!

 

[WrldIntlR]

A lot of my code is "Swing then check for misses" style. It's a bloody mess.

 

[Norie]

Well if you swing an hit that's the important thing.

As long as you hit the right thing of course.

 

[WrldIntlR]

It was all well and good until I thought I had the thing finally conquered and threw the application.visibile = false in there. I thought I was a big bad vb pro, and good lord every single minor bug was a 3 minute force-close, re-open - unprotect nightmare. Just goes to show, always thoroughly test your code before you send it off to your boss and it fails the first time she does something you didn't even think of.

"I swear it was working at my desk" doesn't quite fly.