Introduction | Example | Tutorial | Applications | Comments

Introduction - VBA Close UserForm

Close a UserForm with VBA by using the Unload Me VBA statement. You can also unload userforms with different names by replacing “Me” with the name of your form.

I’ll show you how to close userforms with VBA in this brief tutorial from the VBA Tutorials Blog. At the bottom, you’ll see a macro showing how to use a loop to close all your userforms at once.


Example - VBA Close UserForm

Private Sub cbCancel_Click()
    Unload Me
End Sub

Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheet with just one click.

Show me how I can improve my VBA skills for free

Tutorial - VBA Close UserForm

Close a UserForm from UserForm Event

The macro above only works when you call it from an event procedure on that UserForm - like a command button. That’s because only the UserForm knows that “Me” is the UserForm.

In other words, if you type Unload Me in a regular module, it will fail with an error saying “Invalid use of Me keyword.” Remember, the module doesn’t know what UserForm you want to unload!

Invalid use of Me keyword

Let’s walk through an example showing where it’s appropriate to use “Unload Me” to close your form. Pretend you have a userform that looks like this:

Mouse To Macro Settings UserForm

You may recognize this UserForm from my Mouse To Macro mouse recording Excel Add-in. On this form, I’ve named the Cancel button cbCancel.

When you click the cbCancel button, the form is closed, because I double clicked the cbCancel button during development and pasted the example macro for the click event.

Your macro can be a lot more complicated than this. Feel free to run whatever commands you want before you put the Unload Me statement. “Unload Me” closes your form and removes everything associated with it from memory.


Close UserForm from another Module

This isn’t the first time you’ve seen me use the Unload statement. I used it to demonstrate my VBA Excel splash screen, but if you recall, I didn’t use the “Me” keyword. I could have, but I didn’t.

Let’s say you have a modeless UserForm (ShowModal property set to False), titled UserForm1. When you have a modeless UserForm, you can click around in Excel and run other macros while your UserForm is open. You can interact with things other than your UserForm. This isn’t the default way to show a UserForm.

Anyway, pretend you have this modeless UserForm floating around. You have several other macros that run while the UserForm is just chilling there. To close your UserForm from one of these macros, you would pair “Unload” with the name of your UserForm, like this:

Sub UnloadFormModule()
Unload UserForm1
End Sub

Because the UserForm was named UserForm1, you enter that instead of the Me keyword.


Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheet with just one click.

Show me how I can improve my VBA skills for free

Close All UserForms with VBA

If you want to close all your UserForms, you could create a loop that loops through each form and unloads each form - one by one. Here’s an example:

Sub UnloadAllForms()
Dim tempForm As UserForm
For Each tempForm In UserForms
    Unload tempForm
Next
End Sub

Application Ideas - VBA Close UserForm

I’m going to leave you with an application idea to motivate, inspire and challenge you. A long time ago, I created a modeless UserForm that stayed on the screen until the user pressed a certain key. Once that key was pressed, the form was closed.

Can you do that? Leave a comment below with your macros demonstrating how to do it!

Comments

When you’re ready to start writing your macros faster (without having to search online for answers) check out our best-selling VBA Cheat Sheets for some powerful automation tricks. For more VBA tips, techniques, and tactics, subscribe to our VBA Insiders email series using the form below.

Share this article on Twitter and Facebook, then leave a comment below and let’s have a discussion.


Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheet with just one click.

Show me how I can improve my VBA skills for free