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:

vba excel contact form controls 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

excel userform contacts controls exercise

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.

Even if you have a little difficulty with this last line of code, remember it well because it can be useful every time you need to insert a row below a table (or to know the row number of the last row in a table, and in that case, you don't need to add + 1).

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