Close Excel Userform Using Escape Key - ExcelVbaIsFun

Data Translates
219 followers
13 Video Views·Dec 8, 2022
Hey guys, this is Dan Strong with Excel VBA is Fun.
Coming at you again with another quick tip with Excel VBA.
Today we had a question that said,
Hey, how do you close a user form with the escape key?
And so we're going to address that in a very, very quick fashion.
So let's go ahead and hit Alt F11 to get into Visual Basic Editor.
Alt F11.
Or if you're on a Mac, you can actually use Option F11 or Function Key Option F11.
So here's the Visual Basic Editor.
I've already got a user form there, but I'm going to go ahead and create another user form.
You can click there or you can click the dropdown and go to User Form.
And now I have a user form.
Now, actually, I say that, but really, on a Mac, they don't have very good design mode.
So you actually can't design a user form.
You can run it, but you can't design it on a Mac.
You can on a Windows computer, or you can use a Mac and use Parallels or Boot Camp or something to emulate Windows.
But anyway, here's our first user form here.
And what we're going to do is we're going to simply put a button.
So let's go to Command Button right there.
Excuse me, that's a text box.
We're going to click on Command Button.
And now we have this button.
I'm going to click on it a couple times to type the word, the caption, Close.
You can also go to the caption of that button down here and type in whatever caption you want.
I'm not going to mess with the name of the button because I'm not going to do a whole lot of referencing to it.
But if I double-click on that, I automatically bring up the click event of that button.
And what I want to put in here is something simple.
I want to say unload me.
Now, in this case, the object variable me is kind of dynamic.
It always represents whatever container you're in.
So if you type the word me inside of this user form 2, it always means unload user form 2.
If you typed it in a sheet object or a this workbook object,
then the word me would represent those containers or the same for a module.
In this case, it's just a shorthand of saying unload user form 2, which means we're going to close it.
Now, you could also say me.hide instead of unload me.
And that would simply hide the user form when they click the button rather than completely unloading everything from system memory, from your RAM.
But we're going to go ahead and completely close it down rather than just hiding it and saving its current status.
So let's try that first of all.
I'm going to hit the run button or hit F5.
Here's our really basic, boring user form.
All it does is if you hit the X, of course, it's naturally going to unload it.
But now we have a button that does the same thing.
If I click anywhere else, it doesn't do anything.
If I click here, it unloads the user form.
So that's the first step.
Now, you might say, well, how does that help me with the escape key?
Well, there is something that you can use called the cancel property.
And you can put that on a different number of controls.
But in this case, we're going to put it on this particular control, this command button.
So if I go to the cancel property right here, by default, it is, of course, false.
You can use this dropdown.
I like to put my cursor here and double click to toggle true or false.
So we're going to say the cancel for this control is equal to true.
That means it is the thing that receives the escape key.
So if I had, instead of unload me, I'm going to comment that out.
For example, if I had something like a message box that says hello,
instead, I just want you to know that the escape key is not a catch-all to close the user form.
It's actually just going to run whichever item has the cancel equal to true.
So let's show you that.
First of all, I'm going to hit the escape key right now.
And since cancel equals true on the button, then the button is what is going to run when I hit the escape key.
Even though we're not closing the user form right now, we're actually running a macro that says hello.
So I'm going to hit escape.
And it ran the macro that is associated with the button, which cancel equals true upon.
So if I click it, it does the same thing.
But if I hit the escape key, it triggers that.
So that's the magic.
So now I'm going to double click.
I'm going to comment out with the apostrophe the message box hello
and uncomment out the unload me.
And now you'll see when I run it and hit escape, it actually closes the user form.
See, it's closed.
All right, so that is the gist of it.
If you have any questions, please hit me up in the comments section.
Don't forget to like, subscribe, and click those notification checkbox there.
And we'll see you in the next video.
Now, in the next one, I'm going to give you a tiny teaser here.
We're going to continue on this theme with user forms and keyboard shortcuts.
So we're going to talk about the accelerator.
And you may have noticed that whenever you click on certain things,
that they have this little thing called accelerator.
And you might think, what the heck does that do?
Well, we're going to talk about that and how you can use an alt plus a key combination,
like alt C for close or alt L for close.
And that would run that particular button using the accelerator mode.
So we'll see you in the next video.
And thanks for watching and God bless.
