VBA Course: Controls (Exercise)
Practice Exercise
It's time to put the use of controls into practice with a small exercise.
The file: controls-exercise.xlsm
Here is the starting point of the exercise:

As you may have guessed, the goal is to fill in the table on the sheet using the form.
Here are a few points to consider:
- List the countries based on the list on the second sheet
- Check if all fields have been filled in before adding a new contact
- After adding a contact, reset the form without closing it
Take a moment to work on this exercise before moving on to the solution...
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
Here is one possible solution to complete this exercise.
The "Close" Button
Click event of the Close button:
Private Sub CommandButton_close_Click()
Unload Me
End Sub
Dropdown List Content
Initialize event of the UserForm:
Private Sub UserForm_Initialize()
Dim i As Integer
'Loop to add countries to the dropdown list
For i = 1 To 196
ComboBox_country.AddItem Sheets("Countries").Cells(i, 1)
Next
End Sub
Control Validation
A simple solution to ensure that an option button is selected is to add a default choice (set Value property to True for one of the buttons).
But for the exercise, these controls will be tested like the others.
A possible solution is to display a message box if any of the fields has been missed:
Private Sub CommandButton_add_Click()
'If the form is complete
If (OptionButton_1 Or OptionButton_2 Or OptionButton_3) And TextBox_lastName <> "" And TextBox_firstName <> "" And TextBox_address <> "" And TextBox_city <> "" And ComboBox_country.ListIndex >= 0 Then
'Instructions to insert the contact here...
'If the form is incomplete
Else
MsgBox "Incomplete form"
End If
End Sub
But to make things a little more complicated, each field will be individually tested, and if any of them is not filled, its label will be colored in red:
Private Sub CommandButton_add_Click()
'Set label colors to black (&H80000012 = default color of ForeColor property)
Label_salutation.ForeColor = &H80000012
Label_lastName.ForeColor = &H80000012
Label_firstName.ForeColor = &H80000012
Label_address.ForeColor = &H80000012
Label_city.ForeColor = &H80000012
Label_country.ForeColor = &H80000012
'Check field controls
If OptionButton_1 = False And OptionButton_2 = False And OptionButton_3 = False Then 'If no civility is selected
Label_salutation.ForeColor = RGB(255, 0, 0)
ElseIf TextBox_lastName = "" Then 'If no last name is entered
Label_lastName.ForeColor = RGB(255, 0, 0)
ElseIf TextBox_firstName = "" Then 'If no first name is entered
Label_firstName.ForeColor = RGB(255, 0, 0)
ElseIf TextBox_address = "" Then 'If no address is entered
Label_address.ForeColor = RGB(255, 0, 0)
ElseIf TextBox_city = "" Then 'If no city is entered
Label_city.ForeColor = RGB(255, 0, 0)
ElseIf ComboBox_country.ListIndex = -1 Then 'If no country is selected
Label_country.ForeColor = RGB(255, 0, 0)
Else
'Instructions to insert the contact here...
End If
End Sub

Data Insertion
The following code has been inserted at the specified location in the previous code (as a comment):
Dim row As Integer
'Row number of the first empty cell in column 1 starting from the bottom of the sheet
row = Cells(Rows.Count, 1).End(xlUp).Row + 1
'Salutation choice
If OptionButton_1 Then
Cells(row, 1) = OptionButton_1.Caption
ElseIf OptionButton_2 Then
Cells(row, 1) = OptionButton_2.Caption
Else
Cells(row, 1) = OptionButton_3.Caption
End If
'Insert values into the sheet
Cells(row, 2) = TextBox_lastName
Cells(row, 3) = TextBox_firstName
Cells(row, 4) = TextBox_address
Cells(row, 5) = TextBox_city
Cells(row, 6) = ComboBox_country
'After insertion, reset the form
OptionButton_1 = False
OptionButton_2 = False
OptionButton_3 = False
TextBox_lastName = ""
TextBox_firstName = ""
TextBox_address = ""
TextBox_city = ""
ComboBox_country.ListIndex = -1
In this code, the insertion row number is obtained using:
row = Cells(Rows.Count, 1).End(xlUp).Row + 1
Rows.Count returns the number of rows in the sheet, so Cells(Rows.Count, 1) corresponds to the last cell in column 1.
Starting from this last cell and searching upwards (xlUp), Excel will return the row number of the first non-empty cell in that column (which will be the last completed row of the table). And since we are not searching for the last completed row of the table but the one just below it, we need to add + 1 to that number.
Overview
Finally, here is the complete code along with the file:
'UserForm Initialization
Private Sub UserForm_Initialize()
Dim i As Integer
'Loop to add countries to the dropdown list
For i = 1 To 196
ComboBox_country.AddItem Sheets("Countries").Cells(i, 1)
Next
End Sub
'Add Button
Private Sub CommandButton_add_Click()
'Set label colors to black (&H80000012 = default color of ForeColor property)
Label_salutation.ForeColor = &H80000012
Label_lastName.ForeColor = &H80000012
Label_firstName.ForeColor = &H80000012
Label_address.ForeColor = &H80000012
Label_city.ForeColor = &H80000012
Label_country.ForeColor = &H80000012
'Field controls validation
If OptionButton_1 = False And OptionButton_2 = False And OptionButton_3 = False Then 'If no salutation is selected
Label_salutation.ForeColor = RGB(255, 0, 0)
ElseIf TextBox_lastName = "" Then 'If no last name is entered
Label_lastName.ForeColor = RGB(255, 0, 0)
ElseIf TextBox_firstName = "" Then 'If no first name is entered
Label_firstName.ForeColor = RGB(255, 0, 0)
ElseIf TextBox_address = "" Then 'If no address is entered
Label_address.ForeColor = RGB(255, 0, 0)
ElseIf TextBox_city = "" Then 'If no city is entered
Label_city.ForeColor = RGB(255, 0, 0)
ElseIf ComboBox_country.ListIndex = -1 Then 'If no country is selected
Label_country.ForeColor = RGB(255, 0, 0)
Else
Dim row As Integer
'Row number of the first empty cell in column 1 starting from the bottom of the sheet
row = Cells(Rows.Count, 1).End(xlUp).Row + 1
'Salutation choice
If OptionButton_1 Then
Cells(row, 1) = OptionButton_1.Caption
ElseIf OptionButton_2 Then
Cells(row, 1) = OptionButton_2.Caption
Else
Cells(row, 1) = OptionButton_3.Caption
End If
'Insert values into the sheet
Cells(row, 2) = TextBox_lastName
Cells(row, 3) = TextBox_firstName
Cells(row, 4) = TextBox_address
Cells(row, 5) = TextBox_city
Cells(row, 6) = ComboBox_country
'After insertion, reset the form
OptionButton_1 = False
OptionButton_2 = False
OptionButton_3 = False
TextBox_lastName = ""
TextBox_firstName = ""
TextBox_address = ""
TextBox_city = ""
ComboBox_country.ListIndex = -1
End If
End Sub
'Close Button
Private Sub CommandButton_close_Click()
Unload Me
End Sub
The completed file: controls-completed-exercise.xlsm