VBA Close UserForm

Excel VBA Close UserForm

Earlier we had discussed how to create a UserForm in VBA. Now to get input from the user there was a button for submit, which stored then the value given by the user in an excel sheet. But what after giving the value the form is still there. Or what if the user does not want to give any data and close the userform. We had not discussed how we will close the userform for the user. Similar to submit button to submit the data we had another button which was for cancel which is used to hide the userform. The cancel button also has its own code to hide the userform from the user. Now there are two methods by which we can hide a userform. They are as follows:

1. The first method is when we use the Unload Me method. This method unloads the UserForm and it disappears from the display.
2. Another method is when we use Useform.Hide method. This method hides the UserForm from the display.

It is recommended to use the second method to close the UserForm as the first method will completely unload the UserForm and any data entered by the user will be completely lost. But when we use the second method of hiding the UserForm, then the last entry done on the userform by the user is still present.

How to Close UserForm in Excel VBA?

We will learn about how to close UserForm in Excel VBA with its difference and by a few examples but first, let us learn the method of closing a userform in VBA.

There are two methods to close userform in VBA:

1. Unload Me
2. Hide

Excel VBA Close UserForm – Example #1

First, let us try the method of closing a UserForm using the Unload Me option. We had a userform in our previous article which looks like the one below.

For the submit button we had our code, which stored the data entered by the user in excel. Now double click on the cancel button which will open the code for the same as follows,

Code:

Private Sub CommandButton2_Click()

End Sub

Cancel Button was the second command button we provided to the userform. Now write the code statement as Unload me in the section as shown in the image below.

Code:

Private Sub CommandButton2_Click()

  Unload Me

End Sub

We can see that Me is an Object of Unload statement. Now let us run the userform by pressing the F5 key to display it.

Press Cancel button to see that the userform disappears and it takes us back to the original userform of the project window.

Excel VBA Close UserForm – Example #2

Now let us try the second method which userform.hide method in a similar fashion as above, first, let us open the userform from the userform in the project window.

Now double click the cancel button which will open the view code for the cancel command button.

We already have Unload Me statement present in the cancel command button, clear the code and replace it with the Userform.Hide statement.

Code:

Private Sub CommandButton2_Click()

  UserForm1.Hide

End Sub

Now let us again run the userform by pressing F5 key and display it on the screen.

When we press the cancel button we are again moved to the project window of the userform. Now we all might be wondering that what is the difference between the two methods, as both the methods close the userform for us perfectly then why is the second method most recommended method of both. We will find out exactly why in the next two examples.

Excel VBA Close UserForm – Example #3

Now let us move to the first method Unload Me statement for the cancel button and provide some data to the userform. Double click on the cancel button from the userform to open the view code for the command button and replace the code with the Unload Me statement as follows.

Code:

Private Sub CommandButton2_Click()

  Unload Me

End Sub

Now run the userform again by pressing the F5 key and make it display on the screen.

Let us provide some data to the userform as follows, now don’t press the submit button, press the Cancel button.

Again run the userform by pressing F5 key.

Now we can see that we have lost the data we have provided to the userform since we did not submit it, it was not stored in the worksheet and we closed the userform using the Unload Me statement which removed the data which was already filled.

Excel VBA Close UserForm – Example #4

Now let us do the same exercise but by using Userform.Hide statement,

In the cancel command button replace the code for it using the userform.hide method as shown below.

Code:

Private Sub CommandButton2_Click()

  UserForm1.Hide

End Sub

Now we will again run the userform by pressing the F5 key and provide it with some data as follows.

Now let us press the cancel button, and again run the userform which will give us the following result.

The data is not even lost when we pressed the cancel button if we press submit it will store the data in excel as follows.

Now the data is stored the userform is empty now and we can close the userform using the cancel button,

Things to Remember

• Once the data entry has been done by the user we need to close the userform so it is as important as the submit button.
• Close Userform is in the command button of the userform in VBA, In general, it is named as Cancel or Close.
• There are two separate methods of closing a userform but they eventually serve the same purpose as each other.
• The Userform.Hide method is most recommended as even if the userform is closed by the user before the data entry, the data is not lost and the entry made by the user is again displayed when a user opens the userform.

Recommended Articles

This is a guide to VBA Close UserForm. Here we discuss how to close UserForm in Excel using Unload Me statement and Userform. Hide method in VBA along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Activate Sheet
2. VBA IsError
3. VBA List Box
4. VBA With