|
|
|
| Title | Prevent the user from closing a UserForm by clicking the X button |
|---|
| Description | This example shows how to prevent the user from closing a UserForm by clicking the X button in VBA. |
|---|
| Keywords | UserForm, close, X button, disable X button, VBA, Excel |
|---|
| Categories | Office, 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.
|
| |
|
| |
|
|
