[Excel VBA] How to Prevent a UserForm from Closing (QueryClose Event)

Introduction

When creating an input screen with a UserForm, you often need to control how the form closes. For example, you might want to disable the “X” button until a “Terms of Service” checkbox is checked or until all mandatory fields are filled.

VBA provides a special event called QueryClose that occurs just before a form closes. By using this event, you can cancel (interrupt) the closing process unless specific conditions are met.

In this article, I will explain the smart way to control the closing of a form using the QueryClose event.

Sample VBA Code to Prevent Closing

This macro runs the moment the form tries to close, such as when the user clicks the “X” button in the upper right corner.

In this example, we will create a rule: “Do not let the form close unless the agreement checkbox (AgreeCheckBox) is checked.”

UserForm Code (MainForm)

Write this code directly in the code module of your UserForm.

1. Double-click your UserForm in the VBE to view it.
2. Double-click anywhere on the background of the form to open the code window.
3. Select UserForm from the left dropdown menu and QueryClose from the right dropdown menu.

' Event executed when the form is about to close
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    ' --- If the checkbox (AgreeCheckBox) is unchecked ---
    If Me.AgreeCheckBox.Value = False Then
    
        ' CloseMode = 0 (vbFormControlMenu) indicates the user clicked the "X" button
        If CloseMode = vbFormControlMenu Then
        
            ' Cancel the closing process and show a warning message
            MsgBox "You must agree to the Terms of Service.", vbExclamation, "Confirmation"
            Cancel = True
            
        End If
    End If

End Sub

Code Explanation

Private Sub UserForm_QueryClose(...)

The QueryClose event detects any attempt to close the form. This procedure receives two important arguments:

1. Cancel As Integer: If you set this argument to True, the operation to close the form is cancelled. This is the core of this technique.
2. CloseMode As Integer: This indicates the reason why the form is trying to close.

If CloseMode = vbFormControlMenu Then

There are several values for CloseMode, but the most important one is 0 (constant: vbFormControlMenu). This specifically means the user clicked the “X” button on the form.

If you close the form via code (like Unload Me), the value becomes 1 (vbFormCode). We use the If statement to ensure we only stop the user when they try to close it manually with the “X” button, preventing bugs where your own code can’t close the form.

Cancel = True

This line executes if the “X” button is pressed while the checkbox is unchecked. By setting Cancel to True, you are telling Excel: “Stop the operation to close this form.” As a result, the form remains open.

Summary

In this article, I explained how to use the UserForm_QueryClose event to prevent a form from closing until specific conditions are met.

• The QueryClose event occurs just before the form closes.
• Setting the Cancel argument to True stops the closing action.
• Checking the CloseMode argument allows you to determine why the form is closing (e.g., distinguishing between the “X” button and code execution).

By mastering this event, you can create robust and user-friendly input forms that ensure data integrity and required inputs.