Disabling a UserForm s Close Button

Disabling a UserForm's Close Button

When a UserForm is displayed, clicking the Close button (the — in the upper-right corner) will unload the form. You might have a situation in which you don't want this to happen. For example, you might require that the UserForm be closed only by clicking a particular CommandButton.

Although you can't actually disable the Close button, you can prevent the user from closing a UserForm by clicking it. You can do so by monitoring the UserForm's QueryClose event.

The following procedure, which is located in the code module for the UserForm, is executed before the form is closed (that is, when the QueryClose event occurs):

 Private Sub UserForm_QueryClose _   (Cancel As Integer, CloseMode As Integer)     If CloseMode = vbFormControlMenu Then         MsgBox "Click the OK button to close the form."         Cancel = True     End If End Sub 

The UserForm_QueryClose procedure uses two arguments. The CloseMode argument contains a value that indicates the cause of the QueryClose event. If CloseMode is equal to vbFormControlMenu (a built-in constant), that means that the user clicked the Close button. If a message is displayed, the Cancel argument is set to True , and the form is not actually closed.

CD-ROM  

The example in this section is available on the companion CD-ROM in a file named  queryclose demo.xlsm .

Note  

Keep in mind that a user can press Ctrl+Break to break out of the macro. In this example, pressing Ctrl+Break while the UserForm is displayed causes the UserForm to be dismissed. To prevent this from happening, execute the following statement prior to displaying the UserForm:

 Application.EnableCancelKey = xlDisabled 

Make sure that your application is debugged before you add this statement. Otherwise, you'll find that it's impossible to break out of an accidental endless loop.