VBA - User Forms

A UserForm is a custom-built dialog box that makes a user data entry more controllable for you and easier for the user. In this chapter, we will design a Simple form and add data into excel.
Step 1 : Navigate to VBA Window by pressing Alt+F11 and Navigate to "Insert" Menu and select "User Form". Upon selecting, user form is displayed below.

Step 2 : Now let us design the forms using the given controls.

 

Step 3 : After adding each controls, the controls has to be named. Caption corresponds to what appears on the form and name corresponds to the logical name which will be appearing while we write VBA code for that element.

Step 4 : Below are names against each one of the added controls.

Control	Logical Name	Caption
From	frmempform	Employee Form
Employee ID Label Box	empid	Employee ID
firstname Label Box	firstname	First Name
lastname Label Box	lastname	Last Name
dob Label Box	dob	Date of Birth
mailid Label Box	mailid	Email ID
Passportholder Label Box	Passportholder	Passport Holder
Emp ID Text Box	txtempid	NOT Applicable
First Name Text Box	txtfirstname	NOT Applicable
Last Name Text Box	txtlastname	NOT Applicable
Email ID Text Box	txtemailid	NOT Applicable
Date Combo Box	cmbdate	NOT Applicable
Month Combo Box	cmbmonth	NOT Applicable
Year Combo Box	cmbyear	NOT Applicable
Yes Radio Button	radioyes	Yes
No Radio Button	radiono	No
Submit Button	btnsubmit	Submit
Cancel Button	btncancel	Cancel

Step 5 : Now we will add code for the form load event by performing right click on the form and selecting 'View Code'.

Step 6 : Select userform from the objects drop down and select 'Initialize' method as shown below.

Step 7 : Upon Loading the Form we should ensure that the text boxes are cleared, Drop down boxes are filled and Radio buttons are resetted

Private Sub UserForm_Initialize()

   'Empty Emp ID Text box and Set the Cursor 
   txtempid.Value = ""
   txtempid.SetFocus
   
   'Empty all other text box fields
   txtfirstname.Value = ""
   txtlastname.Value = ""
   txtemailid.Value = ""
   
   'Clear All Date of Birth Related Fields
   cmbdate.Clear
   cmbmonth.Clear
   cmbyear.Clear
   
   'Fill Date Drop Down box - Takes 1 to 31
   With cmbdate
   .AddItem "1"
   .AddItem "2"
   .AddItem "3"
   .AddItem "4"
   .AddItem "5"
   .AddItem "6"
   .AddItem "7"
   .AddItem "8"
   .AddItem "9"
   .AddItem "10"
   .AddItem "11"
   .AddItem "12"
   .AddItem "13"
   .AddItem "14"
   .AddItem "15"
   .AddItem "16"
   .AddItem "17"
   .AddItem "18"
   .AddItem "19"
   .AddItem "20"
   .AddItem "21"
   .AddItem "22"
   .AddItem "23"
   .AddItem "24"
   .AddItem "25"
   .AddItem "26"
   .AddItem "27"
   .AddItem "28"
   .AddItem "29"
   .AddItem "30"
   .AddItem "31"
   End With
   
   'Fill Month Drop Down box - Takes Jan to Dec
   With cmbmonth
   .AddItem "JAN"
   .AddItem "FEB"
   .AddItem "MAR"
   .AddItem "APR"
   .AddItem "MAY"
   .AddItem "JUN"
   .AddItem "JUL"
   .AddItem "AUG"
   .AddItem "SEP"
   .AddItem "OCT"
   .AddItem "NOV"
   .AddItem "DEC"
   End With
   
   
   'Fill Year Drop Down box - Takes 1980 to 2014
   With cmbyear
    .AddItem "1980"
    .AddItem "1981"
    .AddItem "1982"
    .AddItem "1983"
    .AddItem "1984"
    .AddItem "1985"
    .AddItem "1986"
    .AddItem "1987"
    .AddItem "1988"
    .AddItem "1989"
    .AddItem "1990"
    .AddItem "1991"
    .AddItem "1992"
    .AddItem "1993"
    .AddItem "1994"
    .AddItem "1995"
    .AddItem "1996"
    .AddItem "1997"
    .AddItem "1998"
    .AddItem "1999"
    .AddItem "2000"
    .AddItem "2001"
    .AddItem "2002"
    .AddItem "2003"
    .AddItem "2004"
    .AddItem "2005"
    .AddItem "2006"
    .AddItem "2007"
    .AddItem "2008"
    .AddItem "2009"
    .AddItem "2010"
    .AddItem "2011"
    .AddItem "2012"
    .AddItem "2013"
    .AddItem "2014"
   End With
   
   'Reset Radio Button. Set it to False when form loads.
   radioyes.Value = False
   radiono.Value = False

End Sub 

Step 8 : Now we need to add code to the Submit button. Upon Clicking on submit button user Should be able to Add the values into the worksheet.

Private Sub btnsubmit_Click()
  Dim emptyRow As Long
  
  'Make Sheet1 active
  Sheet1.Activate
  
  'Determine emptyRow
  emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
  
  'Transfer information
  Cells(emptyRow, 1).Value = txtempid.Value
  Cells(emptyRow, 2).Value = txtfirstname.Value
  Cells(emptyRow, 3).Value = txtlastname.Value
  Cells(emptyRow, 4).Value = cmbdate.Value & "/" & cmbmonth.Value & "/" & cmbyear.Value
  Cells(emptyRow, 5).Value = txtemailid.Value
  
  If radioyes.Value = True Then
      Cells(emptyRow, 6).Value = "Yes"
  Else
      Cells(emptyRow, 6).Value = "No"
  End If
End Sub

Step 9 : Now add a method to close the form when user clicks on cancel button.

Private Sub btncancel_Click()
    Unload Me
End Sub

Step 10 : Now Let us Execute the Form by clicking on "run" Button. Enter values into the form and click 'Submit' button. Automatically the values would flow into the worksheet as shown below.

 

Positioning of form controls using VBA code – Bug in Excel 2010?

When testing an own developped VBA application in Excel 2010, I noticed a curiosity, which I personally would classify as a bug in Excel 2010.
My application is using some form controls, especially checkboxes which are hidden or unhidden per code. And the cells containing the controls are also hidden or unhidden per code.
The error in Excel 2010 appears when following steps are done:

• Hide the form controls per code (Visible = False).
• Hide the rows containing the controls.
• Save and close the workbook.
• Open the workbook again.
• The form controls are now all on top and lost their original positions.

This problem does not appear in Excel 2007. Now, for getting sure that this is actually a problem of Excel 2010, I wrote a small sample application, which positioned 9 checkboxes as shown in the screenshots below. Please note, that I haved used the german version of Excel for the screenshots as the english version of Excel 2010 is not available yet for me. The properties for each control are set to “Move but don’t size with cells” (in German “Nur von Zellposition abhängig”). The two screenshots below show the worksheet in Excel 2007 and Excel 2010 in the initial state without any influence from VBA code.

 Then I created the two procedures „HideCheckboxes()“ and „ShowCheckboxes()“ in a module and linked them to the two buttons. The relative simple code just hides or unhides the controls and the rows.

Copy  Codefenster   Drucken?

1. Public Sub HideCheckboxes()  
2.     
3.   Dim n As Long  
4.     
5. ' With...  
6.     
7.   With ThisWorkbook.ActiveSheet  
8.     
9. '   Loop...  
10.       
11.     For n = 1 To .Shapes.Count  
12.         
13.       If Not .Shapes(n).FormControlType <> 1 Then  
14.           
15.        .Shapes(n).Visible = False  
16.           
17.       End If  
18.         
19.     Next n  
20.       
21. '   Hide...  
22.       
23.    .Range("$2:$12").EntireRow.Hidden = True  
24.     
25.   End With  
26.     
27. ' Save...  
28.     
29.   ThisWorkbook.Save  
30.     
31. End Sub  
32.   
33. Public Sub ShowCheckboxes()  
34.   
35.   Dim n As Long  
36.     
37. ' With...  
38.     
39.   With ThisWorkbook.ActiveSheet  
40.       
41. '   Loop...  
42.       
43.     For n = 1 To .Shapes.Count  
44.         
45.       If Not .Shapes(n).FormControlType <> 1 Then  
46.           
47.        .Shapes(n).Visible = True  
48.           
49.       End If  
50.         
51.     Next n  
52.     
53. '   Hide...  
54.         
55.    .Range("$2:$12").EntireRow.Hidden = False  
56.     
57.   End With  
58.     
59. ' Save...  
60.   
61.   ThisWorkbook.Save  
62.     
63. End Sub 

 If the procedure linked to the button „Hide“ is called and the workbook saved, closed and reopened, the you can see the results as shown in the following screenshots for Excel 2007 and Excel 2010.

 As you can see, all controls are stacked in Excel 2010. If you display the property „TopLeftCell“ for each control in a MsgBox, you will notice that in Excel 2007 all values ($C$3, $C$4, $C$5, …) are correct. Excel 2010 overwrites this property to $C$2 directly after hiding the controls and if you save and close the workbook, the value $C$2 is saved and all previous values are lost. This logically leads to an incorrect positioning of the controls when openening the file again.

An interesting behavious is that „TopLeftCell“ can be restored unless the workbook is not saved and closed.
The file is stored in the Open XML Format, therefore we can rename the file to *.zip and decompress the file with an unzip application. Depending on whether the file was created with Excel 2007 or Excel 2010, the archive contains different files a folder called „drawings“. The next screenshot shows an excerpt of the content of the two files „drawing1.xml“ and „vmlDrawing1.vml“ for the Excel 2010.

Let have look to the „vmlDrawing1.vml“ file, if you open the file with an text editor you can easily find the sections for the checkboxes.

The word „Anchor“ suggests the cell that is used for positioning the checkbox. And if we compare the values for „Anchor“ from the file for Excel 2007 with the values from the file for Excel 2010, we can notice that after hiding the controls in Excel 2010, all values have been set to „2, 0, 1, 0, 4, 0, 13, 0“. In the file for Excel 2007, these values are „2, 0, 2, 0, 4, 0, 13, 0“ for the first checkbox, „2, 0, 3, 0, 4, 0, 13, 0“ for the second checkbox and so on.
If you manually restore these values in the file „vmlDrawing1.vml“ for Excel 2010, then rebuild a zip file, rename it to a XLSX file and open the file in Excel 2007, you will notice that all the positions of the form controls are restaured. However, if you open the file in Excel 2010, nothing is restaured. I think, the „vmlDrawing1.vml“ file remained for compatibilty reasons in Excel 2010.
The file „drawing1.xml“ also contains some values for the checkboxes, but also the file „sheet1.xml“ for the first sheet in the „worksheets“. The values differs from the values for unhidden controls. I had experimented a little by changing some values to the values for unhidden controls. Unfortunately it was not possible for me to restore the original positions for the controls.
Finally I tested some other form controls. Excel 2010 has the same behaviour if you use comboboxes, radiobuttons or buttons. If you use buttons, it may happen that the buttons are reduced to a line, so someone may think they were removed.
If this behavious was not intentionally developped by the Office 2010 developers, then it’s a bug. Especially since this behavior has from my point of view no significant advantages.
You can avoid this problem by storing the position of the controls an a data sheet, for example, and, when unhidung the controls, set their original positions per code.

Excel Macro Tutorial : Excel Form

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: