Excel 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.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Close UserForm (wallstreetmojo.com)

How to Close UserForm in Excel VBA?

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 subprocedureVBA SubprocedureSUB in VBA is a procedure which contains all the code which automatically gives the statement of end sub and the middle portion is used for coding. Sub statement can be both public and private and the name of the subprocedure is mandatory in VBA.read more like the one below.

In this procedure, we need to write the VBA codeWrite The VBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more 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.

Difference Between Unload & Hide in Excel VBA

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 keyExcel Shortcut KeyAn Excel shortcut is a technique of performing a manual task in a quicker way.read more 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.

This article 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 simple to advanced examples. Below are some useful Excel articles related to VBA: –

  • FileDialog in VBAVBA InStrUse of COUNTIF in VBACall Sub in VBA