VBA Close UserForm

Excel VBA Close Userform

Userforms are vital while getting the inputs from the user as part of the VBA project. We usually design the userform before we present in front of the user. Once the designing of the VBA userform completes we need to show up the same in front of the user and requires VBA coding. Similarly to close the userform it requires VBA coding knowledge.

In this article, we will show you how to close the userform in VBA coding.

How to Close UserForm in Excel VBA?

Once the purpose of the user form is done there is a point in keep showing the userform in front of the user, so we need to close the userform. We can close the userform by using the “Unload Me” statement and “UserForm.Hide” statements. Even though both are slightly different from each other it will serve our purpose eventually.

#1 – Close Userform Using “Unload Me” Statement in VBA

For an example look at the below image of the userform.

I have named the user form as “MyUserForm”.

If I run the userform we will see the userform like below.

I need to fill the required details, once the information filled in if I click on the Submit button it will capture the same data to the worksheet which is visible on the left side.

Upon clicking the submit button it has captured the data that I have entered in the userform.

If you notice we have one more button called “Cancel”. What does this do?

Before we display the userform we need to configure this button. Now I will go back visual basic editor to configure this button.

Now I will double click on Cancel button and it will open up the automatic VBA sub procedure like the below.

In this procedure, we need to write the VBA code about what should happen if we click the Cancel button. When we click on this cancel button it should close the User Form we are working on at the moment.

So, write the code as “Unload Me”.

Code:

Private Sub CancelButton_Click()

   Unload Me

End Sub

“Unload Me” is the word we use to close the userform we are working on. Here user form recognizes the word “Me” as the UserForm itself.

“Unload Me” can be used only on that user form procedure, we cannot call this statement in other modules. If called it will show the error message as “Invalid use of Me Keyword”.

Ok, let’s run the code using F5 key or manually now, we will see blank user form.

Fill the data and click on Submit.

Once the submit button clicked it will store the values to the mentioned cells.

If the data entry is done, we need to close the userform, isn’t it??

So, click on the Cancel button to close the userform, it will close the user form.

#2 – Close UserForm Using Hide Method in Excel VBA

We can also close Userform using “Hide” method as well in VBA. Now once again I will double click on the cancel button to see the private sub procedure.

Since we have already written the code to close the userform we can see the existing code in VBA. Now I will delete this.

To use Hide method we need to call the user form by its name. In this case, our user form name is “MyUserForm”.

After mentioning the user form by its name if we put a dot (.) we can see all the properties and methods of this user form. Now I will select “Hide” method.

Ok, let’s run the userform one more time. We will see blank user form, fill the details first.

Now without a click on the Submit button, I will click the cancel button, it will hide the user form.

Difference Between Unload & Hide in Excel VBA

You must have a question what is the difference between Unload & Hide, where both serves a similar purpose. There is a difference between these two. Now first I will use Unload Me statement. Look at the below image.

I have entered the data in the user form but not yet submitted. If I click on Cancel it will unload the userform.

Now again I will run the code through excel shortcut key F5 or manually, it will display blank userform.

Even though I have entered the data correctly by mistake I have clicked on the cancel button, when the new user form appears again I have filled the data from the scratch.

Now I will use the “Hide” method.

No, I will click on the cancel button, it will hide the visible userform. But when I re-run the macro it will come back with the data that I have already entered on the userform.

This is how we can use the “Unload” statement & “Hide” method to close the userforms in Excel VBA.

Recommended Articles

This has been a guide to VBA Close UserForm. Here we learn how to close Userform using “Unload Me” statement and “Userform.Hide” method in Excel VBA with some simple to advanced examples. Below are some useful excel articles related to VBA –

• FileDialog Option in VBA Excel?
• What is VBA InStr Function?
• What does GetOpenFilename do in VBA?
• List of Comparision Operator for VBA
• Use COUNTIF in VBA
• Call Sub in Excel VBA
• Tutorial in VBA Basics
• VBA Break Point