Excel Macro Tutorial : Excel Form

Sunday, February 08, 2015

Share To :

Tweet

Dear Beginner,

In this article I am going to explain every aspects of VBA Form. User forms in Excel is really nice to see. It looks very fascinating while working with User Forms. You can build a very nice UI (User Interface) using Excel VBA Form. In this Article you are going to learn following things about VBA User Form:

1. How to Create User Form (Creating VBA Form)
2. Showing a VBA Form (Display an Excel Form)
3. How to Initialize a UserForm
4. Hiding a VBA Form
5. Unloading VBA Form
6. Closing VBA Form
7. Example: Creating Data Entry Form using Excel Form
8. Free Download : Data Entry Form using Excel Form

1. How to Create User Form (Creating VBA Form) Creating a Form (Designing a Form) in excel is very easy and interesting. For Designing a Form a single Line Code is not required. Code is required only to make that Form function as per your requirement.

To create a Form, follow the below simple steps:

Step 1. Open your Workbook and Press Alt + F11
Step 2. VB Code Editor will open as below

Step 4. Right Click on Microsoft Excel Object
Step 5. Go to Insert –> User Form 

Step 6. In Right Side you can see one Default UserForm1 Created. You can see the ToolBox as well with all the Controls available for the User Form.

Step 7. Using the Tool Box of the User Form you can Drag and Drop what all Controls you want on your form.

Refer the below User form which I have created to show as an example. You can design by your own how you want.

 2. Showing a VBA Form (Display an Excel Form): As you have seen we have created the User Form in VBA Code. User form will not be displaying on the Excel Sheet by default. To display the form we need to write a piece of code. This is just a single statement to launch an Existing User Form. You can execute that statement to open User form.

UserForm1.Show

You can add the above statement where you want to launch your user form. UserForm1 is the name of the User Form Control.

3. How to Initialize a VBA Form: Before getting in to technical details lets discuss, what is Initialization of User form?

Initialization of User Form is nothing but to do some default operation while launching the Form. At the time of loading the User Form, we set certain values or any kind of defaulting to any field, comes under Initialization.

Whatever code is there in UserForm_Initialize() Event of User Form, will be executed as soon as User Form will be launched (Shown).
Click on the User Form and you will be taken to the below Private Sub with Initialize Event as shown in Image below:

Example: Code:

1. Private Sub UserForm_Initialize()  
2.   
3. UserForm1.ComboBox1.Clear  
4.  With UserForm1.ComboBox1  
5.     .AddItem "Junior Engineer"  
6.     .AddItem "Engineer"  
7.     .AddItem "Team Lead"  
8.     .AddItem "Technical Lead"  
9.     .AddItem "Functional Consultant"  
10.     .AddItem "Technical Consultant"  
11.     .AddItem "Project Manager"  
12.     .AddItem "Delivery Manager"  
13.     .AddItem "Group Delivery Manager"  
14.     .AddItem "Unit Head"  
15.     .AddItem "Regional Head"  
16.  End With  
17. End Sub  

 4. Closing and Hiding and Unloading a VBA Form: Like windows dialog box User Form also has a by default Cross Button to close the form. It will simply close the User form. There is a difference between Closing and hiding a user form.


Closing a User form will close the User Form. All the value entered in to that form will be cleared (lost) when you launch it again. Whereas on hiding a user form all the vaues entered in to that form will remain there and can be seen again once you launch the user form again. Both the terms (Closing and hiding) are pretty much self explanatory.


Closing or Unloading an Excel User form are same. In VBA Code, to close an Excel Form, Unload keyword is used. This is the reason we call it both either closing a VBA Form or Unloading a VBA Form.


Now you know the about Closing, Hiding and Unloading an Excel UserForm. Now i will show you the VBA syntax for Unloading (Closing) and Hiding with an Example.

Hiding a User Form: 

i) Syntax:

<UserForm Name>.Hide

ii) Example:

Private Sub UserForm_Click()
UserForm1.Hide
End Sub

Unloading or Closing a User Form:  i) Syntax: Unload <User Form Name> OR Unload Me ii) Example: Private Sub UserForm_Click() Unload UserForm1 OR Unload Me End Sub

5. Example: Creating Data Entry Form using Excel Form: Now its time for doing some hands-on. I have created one Simple Data Entry Form using Excel VBA Form as shown below in Image. If you wish to see the Code and the whole Workbook download it from below:

Tag : Tech Guides