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 user form, 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 is 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 basic visual 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 a 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 a 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 the Unload Me statement. Look at the below image.

I have entered the data in the user form but have 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 a blank user form.

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 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 user form 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