Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
C# Helper...
 
XML RSS Feed
Follow VBHelper on Twitter
 
 
 
MSDN Visual Basic Community
 
 
 
 
 
TitlePrevent the user from closing a UserForm by clicking the X button
DescriptionThis example shows how to prevent the user from closing a UserForm by clicking the X button in VBA.
KeywordsUserForm, close, X button, disable X button, VBA, Excel
CategoriesOffice, Miscellany
 
This form has OK and Cancel buttons. The user must click one to close the form. It ignores clicks on the form's X button in the right part of the title bar.

The Canceled variable tells the calling code whether the user clicked OK or Cancel.

When the user clicks OK, the program should validate whatever data the user entered (this example assumes the data is valid). If the data is valid, the program sets Canceled to False and hides the form.

When the user clicks Cancel, the program simply sets Canceled to True and hides the form.

The form's QueryClose event handler checks its CloseMode parameter. If the user clicked the form's X button, CloseMode is 0 and the event handler sets its Cancel parameter to True, stopping the close.

 
' Tells the calling code whether the user clicked OK or
' Cancel.
Public Canceled As Boolean

' Close, remembering that the user clicked OK.
Private Sub cmdOk_Click()
Dim data_valid As Boolean

    ' Put data validation code here.
    data_valid = True

    If data_valid Then
        Canceled = False
        Me.Hide
    End If
End Sub

' Close, remembering that we canceled.
Private Sub cmdCancel_Click()
    Canceled = True
    Me.Hide
End Sub

' Don't close if the user clicked the X button.
' CloseMode = 0 when the user clicks that button.
Private Sub UserForm_QueryClose(Cancel As Integer, _
    CloseMode As Integer)
    Cancel = (CloseMode = 0)
End Sub
 
Instead of stopping the close, you could have QueryClose set Canceled to True if the user closes the form by clicking the X button.

For more information on programming Office applications with VBA, see my book Microsoft Office Programming: A Guide for Experienced Developers.

 
 
Copyright © 1997-2006 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated