VBA Close UserForm

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

This article will show you how to close the UserForm in VBA coding.

We must keep showing the UserForm in front of the user completing the purpose of the UserForm. So, we must close the UserForm. We can close the userform by using the "Unload Me" and "UserForm.Hide" statements. Even though both are slightly different, they will eventually serve our purpose.

#1 - Close Userform Using "Unload Me" Statement in VBA

For example, look at the below image of the UserForm.

We have named the UserForm "MyUserForm."

If we run the UserForm, we will see the UserForm like below.

We need to fill in the required details. Once we fill in the information, if we click on the "Submit" button, it will capture the same data to the worksheet, which is visible on the left side.

Clicking the "Submit" button captured the data we had 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. So, we will go back to the basic visual editor to configure this button.

Now, we will double-click on the "Cancel" button. It will open up the automatic VBA subprocedure like the one 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 UserForm we are working on now.

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 the UserForm recognizes the word “Me” as the UserForm itself.

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

Let us run the code using the F5 key or manually now. First, we will see a blank UserForm.

Fill in the data and click on "Submit."

Once we click the "Submit" button, it will store the values to the mentioned cells.

If the data entry is complete, we need to close the UserForm. Isn't it?

So, click on the "Cancel" button to close the UserForm. It will close the UserForm.

#2 - Close UserForm Using Hide Method in Excel VBA

We can also close UserForm using the "Hide" method in VBA. Once again, we will double-click 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. So, now we will delete this.

We need to call the UserForm by its name to use the "Hide" method. In this case, our UserForm name is "MyUserForm."

After mentioning the UserForm by its name, if we put a dot (.), we can see all the properties and methods of this UserForm. Now, we will select the "Hide" method.

Let us run the UserForm one more time. We will see a blank UserForm. Fill in the details first.

Now, without a click on the "Submit" button, we will click the "Cancel" button, which will hide the UserForm.

It would help if you had a question about the difference between "Unload" and "Hide," where both serve a similar purpose. There is a difference between these two. Now first, we will use the "Unload Me" statement. Look at the below image.

We have entered the data in the user form but have not yet submitted it. Therefore, if we click on "Cancel," it will unload the UserForm.

We will run the code through excel shortcut key F5 or manually. It will display a blank UserForm.

Even though we had entered the data correctly by mistake, we clicked on the "Cancel" button. When the new UserForm appeared, we filled the data from scratch.

Now, we will use the "Hide" method.

No, we will click on the "Cancel" button. It will hide the visible UserForm. But, when we re-run the macro, it will come back with the data we have already entered on the UserForm.

Like this,  we can use the "Unload" statement and "Hide" method to close the UserForm in Excel VBA.