CODEWITHSUNDEEP
excelvba
Tom N
Tom N

Reputation: 21

I need Excel to close when I close a UserForm

Been looking for answers all over and none of them work. I need Excel to completely quit when someone tries to close the UserForm.

Currently I have this:

Private Sub UserForm_Terminate()
    ThisWorkbook.Close SaveChanges = False
    Application.Quit
End Sub

Upvotes: 0

Views: 272

Answers (2)

VBasic2008
VBasic2008

Reputation: 54983

Better Safe Than Sorry

If you're really sure there is only one workbook open then you can use this: 

Sub AppQuit()
    ActiveWorkbook.Saved = True
    Application.Quit
End Sub

But if not, you better try this for one or multiple open workbooks (recommended):

Sub AppQuitAll()
    Dim wb As Workbook
    For Each wb In Workbooks
        wb.Saved = True
    Next
    Application.Quit
End Sub

Upvotes: 0

ARickman
ARickman

Reputation: 601

Try the UserForm_QueryClose event. You can determine what code raised this event by checking the value of the CloseMode parameter in this event and then close Excel from there.

See below: 

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    'for reference; you don't need this in your case, but it is useful
    'If ((CloseMode And VBA.VbQueryClose.vbFormCode) = VBA.VbQueryClose.vbFormCode) _               'user clicked button that calls 'Unload Me'
    'Or ((CloseMode And VBA.VbQueryClose.vbFormControlMenu) = VBA.VbQueryClose.vbFormControlMenu) _     'user clicked default Red "X" button 
    'Then   
        'do some stuff 

    'End If

    ExitApp

End Sub

Private Sub ExitApp()

    If Application.Workbooks.Count = 1 Then
        Application.Quit

    Else
        ThisWorkbook.Saved = True
        ThisWorkbook.Close SaveChanges = False

    End If

End Sub


Private Sub btnCancel_Click()

    Unload Me   'this would raise the QueryClose event

End Sub

Upvotes: 1

Related Questions