Waterfall Chart
Repurpose the Red X Close Button on a VBA UserForm
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.
A frequently asked question is "How do I hide the red X close button on the corner of a VBA user form?"
My answer is that you shouldn't hide the red button, since the user knows what it does, or at least what it's supposed to do. Leave the button in place as an obvious way to escape the running procedure. It's your job as programmer to make the escape route work both for the user and for the program.
This userform has code behind the OK and Cancel buttons to hide the form, which keeps it resident in memory. This way, the calling code can interact with the form after it has been dismissed.
The default behavior of the red X in the top right corner is to unload the form, that is, clear it from memory. This is not good, because the calling code will be unable to communicate with the form.
This form has code that repurposes the red X, turning a click on that button to a click on the form's Close button. This is what the user wanted to do anyway.
There are more detailed methods which rely on Windows API calls that will actually disable or hide the red X button, but I don't like to take away a piece of the interface that the user understands.
The code in the userform is shown below. The variable mbCancel is true if the dialog is dismissed with the Cancel button, or false if it is dismissed with the OK button.
-
Option Explicit
-
-
Dim mbCancel As Boolean
-
-
Private Sub btnCancel_Click()
-
mbCancel = True
-
Me.Hide
-
End Sub
-
-
Private Sub btnOK_Click()
-
mbCancel = False
-
Me.Hide
-
End Sub
-
-
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
-
' how was the form closed?
-
' vbFormControlMenu = X in corner of title bar
-
If CloseMode = vbFormControlMenu Then
-
' cancel normal X button behavior
-
Cancel = True
-
-
' run code for click of Cancel button
-
btnCancel_Click
-
End If
-
End Sub
Posted: Monday, March 31st, 2008 under UserForms, VBA.
Comments: 2
Comments
Comment from Dick Kusleika
Time: Tuesday, April 1, 2008, 8:56 am
Do you always mimic the Cancel button? For instance, what if you have user interaction on the form? If a user presses Cancel, they should expect that the form closes and nothing is saved. If the user pressed OK, they should expect that the form closes and everything is saved. If the user presses the red x, however, I think they expect to be warned if the form is dirty. That is, Close and Cancel have different connotations.
I usually program for that with an IsDirty module-level variable, but I find that extra work as distasteful as the code that hides the red x.
Comment from Jon Peltier
Time: Tuesday, April 1, 2008, 9:21 am
Hi Dick -
I don’t *always* mimic the Cancel button, but that’s what usually makes the most sense in my usage. The user decides not to graph that data, or create that pivot table, or export that report, and Cancel fits best of all. If unsaved data is an issue, I may be checking in the Cancel button code anyway. Or my buttons will be labeled “Save”, “Save and Exit”, and “Exit Without Saving”. Best practices include using good, descriptive control labels.
In my experience, it seems to me that the red X is for the user who says “let me the hell out of here!”, so I generally just use Cancel.
Write a comment