[SOLVED] Application.Quit in UserForm attempts to run rest of macro before exiting

Application.Quit in UserForm attempts to run rest of macro before exiting

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.

Solution

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