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
Dick Kusleika says
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.
Jon Peltier says
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.
Wayne H says
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.
Frank says
This was a great article! Thanks!
Tonkawa Jingles says
I use the Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) procedure in Excel 2010 userforms.
I would like to mention because clicking on the Close button, or keying Alt-F4, will always trigger the “UserForm_QueryClose” procedure, i do not use the “If CloseMode = vbFormControlMenu” test.
CloseMode is always 0 and vbFormControlMenu is 0 so why bother?
I learned to program Basic on a 48K Model I and on that platform the line number alone for each line of code consumed 5 bytes of memory so I tend to eliminate as much unnecessary code as possible:)
I put code in the body of the procedure to set Cancel to True or False as meets the need of the form. Granted the code will mimic the Cancel button if it is present.
I would appreciate it if someone detects a flaw in my logic about not needing the If test .
Thanks
Jon Peltier says
The reason to use named constants like “vbFormControlMenu” is readability. Generally you can tell the value of the constant by its name, without having to remember that vbFormControlMenu=0.
This is not a good example, because “Form Control Menu” isn’t very descriptive, and I have learned that testing for 0 is adequate in this case. I probably had to look up “vbFormControlMenu” for this post.
In this day and age, saving characters is meaningless, since hard drives and RAM are so cheap. Using appropriate (descriptive) characters is good practice, because comprehension can be so expensive.
Mark Fisher says
Hi there! Thank you so much for this! It is exactly what i was in need of. I am encountering a problem and hoping you can help?
When I click the Red X, a msgbox pops up and says “Are your Sure….”
When I click “Yes” to exit the userform, nothing happens…. How can I fix this?
Note: I have this code assigned to a button, currently, and it works perfectly.
Jon Peltier says
Mark –
I usually say “Unload Me”, where me refers to the UserForm containing the code.
But that didn’t help, the UserForm was not closed. However, the reason I use the QueryClose event is so I keep the form open, but hidden. I then hide the form in the btnCancel_Click event using “Me.Hide”, and I close the code later, in the procedure that called the UserForm.
If you don’t want to keep the UserForm open for access by the calling code, then don’t put “Cancel = True” in the QueryClose event, instead repeat the Sheet.Select/Range.Select lines in the QueryClose event and remove “Cancel = True”. Or put that in the same If statement, with “Cancel = True” in the Else branch.
Mark Fisher says
THANK YOU!
Mark Fisher says
I am seeing one problem with the Red X repurposing in my file, and was wondering if I could send it to you so that you can see what I am referring to? I am not sure what the best way to get it to you is?
DM says
I know I’m 13 years late to the party, but this is still some very sound advice. It works great when you want to close a form, but some of the text boxes require exact matches and you don’t want to error out when closing the form if there’s nothing or the wrong value in the textbox.
However, I could only get to to work by making my version of the “sub btnCancel” public. I can’t call it in the “Private Sub UserForm_QueryClose” otherwise.
Was the intent to keep all subs private or am I missing something?
Jon Peltier says
Make sure you’re using
Sub btnCancel_Clickand that it is in the code module for the UserForm (the same module as
Sub UserForm_QueryClose).They can be public or private, but it makes the most sense to keep them private.