VBA User Forms Handling QueryClose
Example
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:
| Constant | Description | Value |
|---|---|---|
vbFormControlMenu | Form is closing in response to user action | 0 |
vbFormCode | Form is closing in response to an Unload statement | 1 |
vbAppWindows | Windows session is ending | 2 |
vbAppTaskManager | Windows Task Manager is closing the host application | 3 |
vbFormMDIForm | Not supported in VBA | 4 |
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
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()
Me.Hide
End Sub
Private Sub CancelButton_Click()
this.IsCancelled = True
Me.Hide
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = VbQueryClose.vbFormControlMenu Then
Cancel = True
this.IsCancelled = True
Me.Hide
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
Else
'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.
