How to use QueryClose event in VBA?

In VBA, the QueryClose event is typically associated with UserForms. It allows you to execute code when the form is about to be closed, whether by the user or through VBA code. You can use this event to perform actions such as validation, cleaning up, or even canceling the close operation based on certain conditions. To use the QueryClose event in VBA with a UserForm, follow these steps: 1. Open the VBA Editor by pressing ALT + F11 in Excel.
2. In the Project Explorer, find the UserForm you want to work with. If you don’t have a UserForm yet, you can insert one by right-clicking on any of your VBA project items, choosing Insert, and then selecting UserForm.
3. Double-click the UserForm to open its code window.
4. In the code window, you’ll find a drop-down menu at the top left corner where you can select the UserForm object. Next to it is another drop-down menu where you can choose the QueryClose event.
5. On selecting QueryClose, a stub for the event handler will be created, looking something like this:


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    ' Your code goes here
End Sub

6. Inside this event handler, you can write code based on the Cancel and CloseMode parameters:
* Cancel: A parameter that you can set to non-zero (typically True or 1) to prevent the form from closing.
* CloseMode: Indicates the reason for the QueryClose event being fired. This can be one of the following constants:
0: The user closes the form using the close button (X) or system menu.
1: The Unload statement is being used in VBA code.
2: Windows is logging off.
3: Windows is shutting down.
4: The task manager is closing the application. Here’s an example of how you might use the QueryClose event to confirm before closing the form:


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    ' Only prompt when the user is closing the form (not for VBA Unload)
    If CloseMode = 0 Then
        If MsgBox("Are you sure you want to close?", vbYesNo + vbQuestion) = vbNo Then
            ' Prevent the form from closing
            Cancel = True
        End If
    End If
End Sub

Remember to check the reason for closure with the CloseMode parameter and apply your conditions or validations before deciding whether to set the Cancel parameter, thus preventing the form from closing.

facebooklinkedin