VBA Close UserForm

When we make a userform it takes data as input from users, but the data is provided to the form doesn’t close itself, so it can mislead user to input data again, we use two different commands to close a userform when the input has been given and they are Unload me method to close a userform or we can use userform.hide method.

Excel VBA Close Userform

Userforms are vital while getting inputs from the user as part of the VBA project. We usually design the user form before we present it in front of the user. Once the designing of the VBA user form completes we need to show up the same in front of the user and require 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 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 the Cancel button and it will open up the automatic VBA subprocedure 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 the 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 the “Hide” method as well in VBA. Now once again I will double click on the cancel button to see the private subprocedure.

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 the 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 the “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 serve 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 it. 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 the “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 in VBA
• VBA InStr
• Use of COUNTIF in VBA
• Call Sub in VBA