VBA Creating, Displaying, Uploading UserForms

UserForm is a customized interface and acts as a VBA container and can add various controls as per the required functionality, each of which has certain usage and related properties. You can create your own dialog boxes with this feature. Your project can have n number of userforms wherein each userform has its own dialog own.

Creating a Userform

There are two ways to install a UserForm in VBA, which are as follows. 

Process 1: Right Click on VBAProject ->Click on insert -> Click on Userform.

Process 2: Click on Insert from the ribbon bar-> Click on Userform.

In both the process, you will notice a folder has been introduced with name Forms (inside which it consists the UserForm) in the VBA Project. Further clicking on the UserForm will straightway lead you to a window entitled UserForm1 (though the name can be changed from the properties) following with a “controls” toolbox.

Adding control to Userform

Controls are added to a UserForm to accept the inputs from the user and make it more user-friendly. The control box can be resized by stretching the adjustment handles and can be dragged to any position. You can even adjust the spacing and the alignment of the UserForm. There are two ways to add the controls to your useform:

• Select the required control and drag it to the Userform. 
• The Second method is to left-click on the control available in the ToolBox, in the form left-click, and the control will be automatically added.

Example: Create a VBA userform, which takes the basic information from the user and update it on the excel sheet. Refer to the below image to get a glimpse of the form.

Private Sub UserForm_Initialize()
 'Setting the values for each form control. The UserForm gets loaded wherever the program is run. 
 Dim i As Integer
 Dim myArray As Variant
 'in the list box setting the age values and categorizing them from 17 to 50.
 With Me.ListBox1
 For i = 17 To 50
 .AddItem i & " yrs"
 Next i
 End With
 'adding the cities into combo box
 myArray = Array("Mumbai", "Banglore", "Delhi", "Noida", "Gurugram", "Chennai", "Kolkata", "Patna", "Hyderabad", "Ahmedabaad")
 Me.ComboBox1.List = myArray 
 ‘so as only one option could be selected
 OptionButton1.GroupName = "Gender"
 OptionButton2.GroupName = "Gender"
 ‘disabling the text field for the spouse so it can only work if the user has checked the married checkbox
 Me.spouse_name.Enabled = False
 End Sub
 ‘double click on the married checkbox the write the following code in the sub procedure
 Private Sub CheckBox1_Click()
 'user can only enter in spouse_name TextBox if the CheckBox is selected
 If CheckBox1.Value = True Then
     spouse_name.Enabled = True
 Else
     spouse_name.Enabled = False
 End If
 End Sub
 ‘double click on the clear button and write the following code in sub procedure
 Private Sub CommandButton2_Click()
   ‘clearing the value of name text box
 TextBox1.Value = ""
 spouse_name.Value = ""
 'clearing the value for OptionButton  "Male"
 OptionButton1.Value = False
 'clearing the value for OptionButton  "Female"
 OptionButton2.Value = False
 'clearing the ListBox value for Age selection
 ListBox1.ListIndex = -1
 'clearing the values in ComboBox for Country 
 ComboBox1.Value = ""
 'CheckBox to select if Married
 CheckBox1.Value = False
 End Sub
 ‘double click on the save button and type the following code in the sub procedure
 Private Sub CommandButton1_Click()
 Dim totalRows As Long
 Dim str As String
 Dim endDate As String, startDate As String
 'error check - blank UserForm fields not permitted 
 If TextBox1.Text = "" Then
 MsgBox "Name can’t be black", vbOKOnly, "Name Error!"
 Exit Sub
 ElseIf OptionButton1.Value = False And OptionButton2.Value = False Then
 MsgBox "Gender can’t be blank!", vbOKOnly, "Gender Error!"
 Exit Sub 
 ElseIf ListBox1.ListIndex = -1 Then
 MsgBox "Age can’t be blank!", vbOKOnly, "Age Error!"
 Exit Sub
 ElseIf ComboBox1.Value = "" Then
 MsgBox "Country can’t be blank!", vbOKOnly, "Country Error!"
 Exit Sub
 ElseIf CheckBox1.Value = True And spouse_name.Text = "" Then 
 MsgBox "Spouse Name can’t be blank", vbOKOnly, "Spouse Error!"
 Exit Sub
 End If
  ‘finding the last entered row
 totalRows = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
 If totalRows < 3 Then
 totalRows = 3
 Else
 totalRows = totalRows
 End If
 ‘copying the form values in the excel sheet:
 Sheet1.Cells(totalRows + 1, 1) = TextBox1.Text
 If OptionButton1.Value = True Then
 Sheet1.Cells(totalRows + 1, 2) = "Male"
 ElseIf OptionButton2.Value = True Then
 Sheet1.Cells(totalRows + 1, 2) = "Female"
 End If
 Sheet1.Cells(totalRows + 1, 3) = ListBox1.Value
 If spouse_name.Text <> "" Then
 Sheet1.Cells(totalRows + 1, 4) = spouse_name.Text
 Else
 Sheet1.Cells(totalRows + 1, 4) = "Unmarried"
 End If
 Sheet1.Cells(totalRows + 1, 5) = ComboBox1.Value
 'arranging and sorting all the data stored in excel sheet1, by name in alphabetic order
 Sheet1.Range("A4:H" & totalRows + 1).Sort Key1:=Sheet1.Range("A4"), Order1:=xlAscending
 'Clearing all the text
 TextBox1.Value = ""
 spouse_name.Value = ""
 OptionButton1.Value = False 
 OptionButton2.Value = False
 ListBox1.ListIndex = -1
 ComboBox1.Value = "" 
 CheckBox1.Value = False
 End Sub 

Output

Case 1: Fill all the details and press the save button. The excel sheet will be update with the inserted values. Repeat it two three times and you notice the names are present in the sorted order.

Case 2: Keep any field unfilled, and you will notice and message error pops out notifying that blank field can’t be entertained.