Prevent a userform from closing when the user clicks the x-button by using VBA in Microsoft Excel

In this article, we will show how to disable the x-button on the userform by using VBA.

In this example, we have created a userform which can be activated by clicking the run button on the worksheet.

This userform contains only one button which is used for closing the userform. Userform can be closed only by clicking this button. Even, pressing shortcut key Alt+F4 on the keyboard will not be able to close this userform.

Logic explanation

For disabling the x-button of the userform, we have used query close event of the userform. This event is fired before closing of the userform.

In this event, we have set condition that if closing mode is menu control then do not close the userform; instead display an information message in the dialog box.

Please follow below for the code

Option Explicit

Sub running()

UserForm1.Show

End Sub

'Add below code in userform


Private Sub CommandButton1_Click()

'Close the userform
Unload Me

End Sub



Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Using Query Close event of Userform


'Comparing the constant value of CloseMode variable
'If it is equal to inbuilt constant of control menu
'Then prevent closing of userform and
'Display information message dialog box
If CloseMode = vbFormControlMenu Then
    
    'Changing Cancel variable value to True
    'By default, it is False
    Cancel = True
    MsgBox "You can't close the dialog like this!"
End If

End Sub

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com