2

My question is: using VBA in Excel 2013 how can I gracefully close an entire instance of Excel when the user decides they don't want to fill out a UserForm and clicks quit or cancel?

Currently, if the user clicks quit or cancel, I check to see if my instance is the only one open. If it is not, I can use ThisWorkbook.Close and I think I will be okay. However, if it is, I do not want the application to still be present, so I used Application.Quit. This, though, tries to finish running the macro, throws errors (originally "type mismatch" because I unload the form), and only closes after I click "Debug" or "End" (which it does so fast for either I cannot actually debug). I'm ignoring the first case for now and just trying to exit the entire application. It's a very long macro with a lot of subroutines and functions, so for debugging and posting here, I have shortened it. The type mismatch error no longer occurs, but I believe that was a consequence of the actual error: code running after the command to close the application is called.

First, here's the code that starts everything:

Private Sub CommandButton1_Click()

    Call form_variables
    frm_REQUEST.Show
    Call a_REQUEST_main

End Sub

The subroutine

form_variables

is a subroutine that creates public variables so I can store the data from the UserForm.

frm_REQUEST.Show

initializes (including calling a function that finds another workbook, extracts a list, does some formatting, closes the workbook and enters the list into the userforms drop down box) and shows the form, and finally

a_REQUEST_main

uses the public variables (where UserForm data is stored) and does its thing (but shouldn't do anything if the UserForm is closed).

The code that is executed when .Show is called is:

Private Sub UserForm_Initialize()

    ' Get job numbers from other workbook
    Dim job_selection_list As Variant
    job_selection_list = get_job_list()

    With frm_REQUEST.Job_Number_ComboBox
        .List = job_selection_list
    End With

    ' set focus on Job Numbers
    JN_combobox.SetFocus

End Sub

Private Sub cancel_button_Click()

    Set job_selection_list = Nothing

    Unload Me

    Application.Quit

End Sub


Private Sub submit_button_Click()

    ' Values from userform saved as global (?) variables so other subroutines can access.

End Sub

I stepped through the program and saw that, once Application.Quit is called in the UserForm, the macro, in the main subroutine, steps to 

Call a_REQUEST_main

but it should really just close everything out. I tried doing "save" commands, and changing the order of things, and read about objects needing to be set to nothing (hence the setting of the job_selection_list which is created when the drop down list is initialized), but I cannot seem to get this to work, or find anything online. Can anyone provide some guidance or let me know of a better way to close an excel instance? Help me Stack-Overflow Kenobi, you're my only hope!

Thanks.

Community
  • 1
  • 1
TenaciousSisyphus
  • 23
  • 1
  • 4
  • Another question: why bother with a user-defined "cancel" button when the UserForm already has a cancel button that will trigger the `QueryClose` and/or `Terminate` procedure, if you have them. You're making this unnecessarily complicated, I think, by trying to replicate a built-in event handler with a button. – David Zemens Jun 20 '14 at 20:45
  • This probably sounds wishy-washy, but from a UI standpoint, I think my user-base would rather have a nice button that says "Cancel" so I added it. Then, disabling the functionality of the Close seemed unnecessarily complicated, so I just put `Call cancel_button_Click` in the `QueryClose` code block, but as I'll explain in response to your comment below, I don't think any of that really mattered... – TenaciousSisyphus Jun 20 '14 at 20:55
  • The `QueryClose` handler is really only necessary if you need to trap some condition that would cause the `Terminate` to **not** happen. If you don't have anything substantial in that event, just use the `Terminate` handler all by itself :) – David Zemens Jun 20 '14 at 21:30

2 Answers2

0

Put Application.Quit in the form's Terminate event handler instead of in the button's Click event handler.

The reason is that clearly the procedure will keep running even if the form has unloaded. So use the events to your advantage.

Putting it in the Click event will unload the form, but the procedure will keep running which of course may raise errors or other undesired effects.

Note: You may be prompted to save/discard changes (if any) to the workbook.

David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • I didn't include it in the code above because it seemed to be getting too long, but I actually have – TenaciousSisyphus Jun 20 '14 at 20:24
  • Do that **instead of** where you currently have it. – David Zemens Jun 20 '14 at 20:28
  • I didn't include it in the code above because it seemed to be getting too long, but I have the `Terminate`, `QueryClose`, and `cancel_button_Click` all with the same userform. I continued to try and debug after I posted and realized that the `Unload Me` in the `cancel_button_Click` takes execution to the `QueryClose` code block and continues on into the `Terminate` (which is right beneath it), then back to the `cancel_button_Click`, ultimately blasting its way through _three_ `Exit Sub` commands! – TenaciousSisyphus Jun 20 '14 at 20:38
  • Doesn't really matter whether you *have* these procedures... without know what's in them I can't really help you. What's *in them*? Get rid of `Application.QUit` in the button click handler as I suggest above. Get rid of it entirely in that procedure. And put it in the `Terminate` handler. You can get rid of the `QueryClose` event unless you are actively using that for some other checks. – David Zemens Jun 20 '14 at 20:42
  • BTW: "blasting through" `Exit Sub`? What else would you expect it to do? `Exit Sub` simply ends the current procedure and returns to the calling procedure, if any. – David Zemens Jun 20 '14 at 20:44
  • I ultimately took Sorceri's approach, but wanted to explain my reasoning in case it helps anyone else. Getting rid of `Application.Quit` wouldn't help because, stepping through the execution, I go from `Unload Me` (the first line) in the `cancel_button_Click` sub to the start of the `Terminate` sub, so the `Application.Quit` in the `cancel_button_Click` is not executed until _after_ its executed in the `Terminate` block (but, of course, it quits before that happens). – TenaciousSisyphus Jun 20 '14 at 20:58
  • The "blasting through" was strange to me because I had thought exiting the sub would get me out of the whole userform. Thinking about it now, it seems obvious it would just drop down to the next sub... Anyway, thanks for the help and insight! – TenaciousSisyphus Jun 20 '14 at 21:02
  • I didn't say "get rid of Application.Quit" altogether... I said specifically and clearly to remove it from where you have it (in the button `Click` event and anywhere else you may errantly have placed it...) and put it **only** in the `Terminate` event. Your button click will then unload the form, triggering the other event(s) (`QueryClose` and/or `Terminate`) which then quits the application... Sorceri's approach works, to be sure, but it's unnecessarily complicated. – David Zemens Jun 20 '14 at 21:40
0

Just add a variable to account for when the user closes the form

in the form

'hold flag if users cancels form
Public btnCancel As Boolean
Private Sub CommandButton1_Click()
Unload Me
btnCancel = True
End Sub

'set the flag each time the form active
Private Sub UserForm_Activate()
btnCancel = False
End Sub

then in your code

Call form_variables
frm_REQUEST.Show

If frm_REQUEST.btnCancel Then
    Application.Quit
Else
    Call a_REQUEST_main
End If
Sorceri
  • 7,870
  • 1
  • 29
  • 38