VBA Express : Excel - Disable the X (Close) Button on a Userform

Excel

Disable the X (Close) Button on a Userform

Ease of Use

Intermediate

Version tested with

2002

Submitted by:

Jacob Hilderbrand

Description:

This macro disables the X Button and forces the user to close the UserForm by pressing a Command Button on the form.

Discussion:

You have a UserForm and you do not want the user to close it with the X Button. This macro will stop the X Button from closing the UserForm. This code is useful if your command button runs code that verifies entries in texboxes, or you need to verify that an option on the userform has been chosen.

Code:

instructions for use

'Place this code in a Module Sub ShowForm() UserForm1.Show End Sub 'Place this code in a UserForm with one Command Button named CommandButton1. Option Explicit Private Sub CommandButton1_Click() Unload Me End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = 0 Then Cancel = True MsgBox "The X is disabled, please use a button on the form.", vbCritical End If End Sub

How to use:

Open Excel.
Alt + F11 to open the VBE.
Insert | Module.
Paste the code from above that is designated for the Module.
Insert | UserForm.
Double click the userform and paste the code from above designated for the UserForm.
Add one command button to the UserForm from the Control Toolbox.
Close the VBE (Alt + Q or press the X in the top right corner).

Test the code:

Tools | Macro | Macros...
Select ShowForm and press Run.

Sample File:

Disable X.zip 8.19KB

Approved by mdmackillop

This entry has been viewed 383 times.