WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Close UserForm

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.

VBA Close Userform

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.

You can download this VBA Close UserForm Excel Template here – VBA Close UserForm Excel Template

#1 – Close Userform Using “Unload Me” Statement in VBA

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

VBA Close UserForm Example 1

I have named the user form as “MyUserForm.”

If I run the userform, we will see the userform like below.

VBA Close UserForm Example 1-1

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.

VBA Close UserForm Example 1-6

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.

VBA Close UserForm Example 1-7

Now I will double click on the Cancel button, and it will open up the automatic VBA subprocedure like the below.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

VBA Close UserForm Example 1-2

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

VBA Close UserForm Example 1-3

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.

VBA Close UserForm Example 1-8

Fill the data and click on Submit.

VBA Close UserForm Example 1-4

Once the submit button clicked, it will store the values to the mentioned cells.

VBA Close UserForm Example 1-5

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.

VBA Close UserForm Example 1-3

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

VBA CUF Example 1-9

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.

VBA CUF Example 2-2

Ok, let’s run the userform one more time. We will see a blank user form, fill the details first.

VBA CUF Example 2-3

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.

VBA CUF Example 3

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.

VBA CUF Example 3-1

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.

VBA CUF Example 3-2

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
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Close UserForm Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More