VBA Course: Controls (exercise)

Now let's try a little exercise to practice using controls ...

This is how we start the exercise:

Here is the downloadable Excel file: controls_exercise.xls

By now it should be clear that the goal here is to fill the table using the form.

A few thinks to keep in mind:

List the countries based on the list on the second worksheet
Verify the contents of the controls before adding a new contact
After inserting a value, reinitialize the values of the controls without closing the form

Take a moment to work through this exercise before looking at the solution ...

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

This is one way that you could solve this problem

Our first action should be to increase the Zoom property of the UserForm to 120 to make it easier to use the form:

We have already covered option button tests (in the first controls page), so we are using a simpler solution here.

"Mrs" is chosen by default (Value property: True), which means that we don't have to verify that a salutation has been chosen.

The "Close" button

Private Sub CommandButton_Close_Click()
    Unload Me
End Sub

The contents of the drop-down list

Private Sub UserForm_Initialize() 'Loading the list when the UserForm is opened
    For i = 1 To 252 'List of 252 countries froim the "Country" worksheet
        ComboBox_Country.AddItem Sheets("Country").Cells(i, 1)
    Next
End Sub

Verification of controls

One simple solution would be to display a dialog box if any of the controls is empty:

Private Sub CommandButton_Add_Click()
    If TextBox_Last_Name.Value = "" Or TextBox_First_Name.Value = "" Or TextBox_Address.Value = "" Or TextBox_Place.Value = "" Or ComboBox_Country.Value = "" Then
        MsgBox "Form incomplete"
    Else
        'Instructions for inserting a contact here ...
    End If
End Sub

But to make things a bit more complicated, each control should be tested individually, and if any of them is empty, its Label text color should be changed to red:

Private Sub CommandButton_Add_Click()
    'Setting Label text color to black
    Label_Last_Name.ForeColor = RGB(0, 0, 0)
    Label_First_Name.ForeColor = RGB(0, 0, 0)
    Label_Address.ForeColor = RGB(0, 0, 0)
    Label_Place.ForeColor = RGB(0, 0, 0)
    Label_Country.ForeColor = RGB(0, 0, 0)

    'Content controls
    If TextBox_Last_Name.Value = "" Then 'IF no "name" provided ...
        Label_Last_Name.ForeColor = RGB(255, 0, 0) 'Set Label "name" color to red
    ElseIf TextBox_First_Name.Value = "" Then
        Label_First_Name.ForeColor = RGB(255, 0, 0)
    ElseIf TextBox_Address.Value = "" Then
        Label_Address.ForeColor = RGB(255, 0, 0)
    ElseIf TextBox_Place.Value = "" Then
        Label_Place.ForeColor = RGB(255, 0, 0)
    ElseIf ComboBox_Country.Value = "" Then
        Label_Country.ForeColor = RGB(255, 0, 0)
    Else
        'Instructions for inserting a contact here ...
    End If
End Sub

Inserting data

The following code should be inserted in the place indicated in the code above (see commentary):

Dim row_number As Integer, salutation As String

'Choice of Salutation
For Each salutation_button In Frame_Salutation.Controls
    If salutation_button.Value Then
        salutation = salutation_button.Caption 'Salutation chosen
    End If
Next

'row_number = row number of the last non-empty cell in the column +1
row_number = Range("A65536").End(xlUp).Row + 1

'Inserting values into the worksheet
Cells(row_number, 1) = salutation
Cells(row_number, 2) = TextBox_Last_Name.Value
Cells(row_number, 3) = TextBox_First_Name.Value
Cells(row_number, 4) = TextBox_Address.Value
Cells(row_number, 5) = TextBox_Place.Value
Cells(row_number, 6) = ComboBox_Country.Value

'After insertion, the initial values are replaced
OptionButton1.Value = True
TextBox_Last_Name.Value = ""
TextBox_First_Name.Value = ""
TextBox_Address.Value = ""
TextBox_Place.Value = ""
ComboBox_Country.ListIndex = -1

Overall view

That's all, and here you have the complete code for the exercise and the downloadable Excel file:

Private Sub CommandButton_Close_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize() 'List of 252 countries on the "Country" worksheet
    For i = 1 To 252
        ComboBox_Country.AddItem Sheets("Country").Cells(i, 1)
    Next
End Sub

Private Sub CommandButton_Add_Click()
    'Setting Label color to black
    Label_Last_Name.ForeColor = RGB(0, 0, 0)
    Label_First_Name.ForeColor = RGB(0, 0, 0)
    Label_Address.ForeColor = RGB(0, 0, 0)
    Label_Place.ForeColor = RGB(0, 0, 0)
    Label_Country.ForeColor = RGB(0, 0, 0)

    'Content controls
    If TextBox_Last_Name.Value = "" Then 'If no "name" provided ...
        Label_Last_Name.ForeColor = RGB(255, 0, 0) 'Set Label "name" color to red
    ElseIf TextBox_First_Name.Value = "" Then
        Label_First_Name.ForeColor = RGB(255, 0, 0)
    ElseIf TextBox_Address.Value = "" Then
        Label_Address.ForeColor = RGB(255, 0, 0)
    ElseIf TextBox_Place.Value = "" Then
        Label_Place.ForeColor = RGB(255, 0, 0)
    ElseIf ComboBox_Country.Value = "" Then
        Label_Country.ForeColor = RGB(255, 0, 0)
    Else
        'If the form is complete, the values will be inserted onto the worksheet
        Dim row_number As Integer, salutation As String
        
        'Choice of salutation
        For Each salutation_button In Frame_Salutation.Controls
            If salutation_button.Value Then
                salutation = salutation_button.Caption
            End If
        Next

        'row_number = row number of the last non-empty cell in column +1
        row_number = Range("A65536").End(xlUp).Row + 1

        'Inserting values onto the worksheet
        Cells(row_number, 1) = salutation
        Cells(row_number, 2) = TextBox_Last_Name.Value
        Cells(row_number, 3) = TextBox_First_Name.Value
        Cells(row_number, 4) = TextBox_Address.Value
        Cells(row_number, 5) = TextBox_Place.Value
        Cells(row_number, 6) = ComboBox_Country.Value
        
        'After insert, we replace the initial values
        OptionButton1.Value = True
        TextBox_Last_Name.Value = ""
        TextBox_First_Name.Value = ""
        TextBox_Address.Value = ""
        TextBox_Place.Value = ""
        ComboBox_Country.ListIndex = -1
    End If
End Sub

Here is the downloadable Excel file: controls_exercise2.xls