How to disable close button userform?

In this Excel VBA tutorial lesson, I will show you how to disable the close button in an Excel userform. Let us first look at the close button we use in Excel userforms:

The button

disable close useform

The red button at the top end of the userform is the close button. Sometimes we want to disable this button so that the user can only close the userform using the exit button that is already created in the userform for the same purpose.

The code

Let’s start with the code to disable the close button:

Private Sub UserForm_QueryClose
(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then 
    Cancel = True
    MsgBox "Please use the Exit button
 to close the form", vbOKOnly
End If
End Sub

This code uses the “QueryClose” event of the UserForm to cancel the close operation when the close button is clicked. The CloseMode parameter determines how the UserForm is being closed. If the value of CloseMode is vbFormControlMenu, then it means that the close button was clicked. In this case, the code sets the Cancel parameter to True to cancel the close operation.

You can paste this code into the code module for the UserForm to disable the close button.

vba code useform close

When we use this code and try to press the close button, it will prompt a message like shown below:

useform msgbox message

This Message can be changed in the following code line:

MsgBox “Please use the Exit button to close the form”, vbOKOnly

The purpose for creating this functionality is that sometimes we have other conditions before exiting the doc like:

See also  How to Declare Variable in Excel VBA

The user must enter data before closing.

On clicking the Exit button:

please enter name before exiting

For this function, we want to disable the close button.