Repurpose the Red X Close Button on a VBA UserForm
by Jon Peltier
Monday, March 31st, 2008
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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
Related Posts:
- Alternative to Excel’s Flaky RefEdit Control
- Build an Excel Add-In 4 – Create the Dialog
- How To: Assign a Macro to a Button or Shape
- Validation Functions
- Chart Event to Highlight a Series
- Chart Event Class Module to Highlight a Series
- Robust VBA Save-As Technique
- Using RefEdit Controls in Excel Dialogs
- Build an Excel Add-In 5 – Tie the Code Together
- Get Open or Save-As Filename
Posted: Monday, March 31st, 2008 under VBA.
Comments: 3
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.
Comment from Wayne H
Time: Wednesday, October 28, 2009, 1:30 am
I just found this blog since I was looking for the official word on what the red X button should do in Windows UI. I had always thought that the red X should Close the dialog (if the dialog also has a Close button or no buttons) and it should Cancel if the dialog has OK/Cancel. So in this case I would agree that using the red X to cancel is correct.
This is what MS say about the Red X for dialogs: “The Close button on the title bar should have the same effect as the Cancel or Close button within the dialog box. Never give it the same effect as OK.”
So to respond to Dick’s comment, I would say that if your Cancel button prompts the user in the case of “IsDirty = true” then the red X should have the same effect. That is, if your form has a Cancel button, the red X should cancel and if it has a Close (or no button at all) it should Close. It sounds, however like your Cancel button does NOT prompt in which case to me it seems like you have a different behavior for the red X almost as if you want to ensure your users really meant to exit via that route. I guess that issue is more subjective. We don’t typically do this (red X cancels with no prompt if there is a Cancel button on the dialog), but I wonder if we are giving our users too much credit for knowing that red X will abandon changes for those use cases.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.