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.

ArrowRawSheet

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.

ArrowCloseForm

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.

ArrowClickXButton

 

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

Users are saying about us...

  1. I stumbled across this article when I was looking for information about how to programmatically show a form when a workbook is opened. Being concerned that my users could close the form, effectively rendering the application useless, I was delighted, though not surprised, to so easily find a way to keep that from happening.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube