VBA User Forms Handling QueryClose


The QueryClose event is raised whenever a form is about to be closed, whether it's via user action or programmatically. The CloseMode parameter contains a VbQueryClose enum value that indicates how the form was closed:

vbFormControlMenuForm is closing in response to user action0
vbFormCodeForm is closing in response to an Unload statement1
vbAppWindowsWindows session is ending2
vbAppTaskManagerWindows Task Manager is closing the host application3
vbFormMDIFormNot supported in VBA4

For better readability, it's best to use these constants instead of using their value directly.

A Cancellable UserForm

Given a form with a Cancel button

some sample form

The form's code-behind could look like this:

Option Explicit
Private Type TView
    IsCancelled As Boolean
    SomeOtherSetting As Boolean
    'other properties skipped for brievety
End Type
Private this As TView

Public Property Get IsCancelled() As Boolean
    IsCancelled = this.IsCancelled
End Property

Public Property Get SomeOtherSetting() As Boolean
    SomeOtherSetting = this.SomeOtherSetting
End Property

'...more properties...

Private Sub SomeOtherSettingInput_Change()
    this.SomeOtherSetting = CBool(SomeOtherSettingInput.Value)
End Sub

Private Sub OkButton_Click()
End Sub

Private Sub CancelButton_Click()
    this.IsCancelled = True
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        Cancel = True
        this.IsCancelled = True
    End If
End Sub

The calling code could then display the form, and know whether it was cancelled:

Public Sub DoSomething()
    With New UserForm1
        .Show vbModal
        If .IsCancelled Then Exit Sub
        If .SomeOtherSetting Then
            'setting is enabled
            'setting is disabled
        End If
    End With
End Sub

The IsCancelled property returns True when the Cancel button is clicked, or when the user closes the form using the control box.