VBA-Lesson 12.4. Control elements (Exercises)

Now let's look at a small exercise to practice using the controls.

There is a file with a ready-made form and control elements. We need to write code that would make all the elements work so that the user can populate our improvised database through interaction with the form.

Here is the Excel file for download: controls_exercise.xls

Now it should be clear that the goal here is to populate the table using the form.

A few points to consider:

• The list of countries is based on the list on the second sheet;
• Checking the values of the controls before adding a new contact;
• After entering the values, return the initial values of the controls without closing the form.

One of the ways to 'solve the problem

First, we need to increase the Zoom property of our form to 120 to make it easier to use:

We've already covered checking option buttons (in the first lesson on controls), so here we use a simple solution.

"Mrs" is selected by default (Value property: True), that is, we will not check the address when selecting it.

Close button (Close)

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 form is open
     For i = 1 To 252 'Formation of a list of 252 countries from the worksheet "Country"
        ComboBox_Country.AddItem Sheets("Country").Cells(i, 1)
     Next
End Sub

Checking controls

It would be a simple solution to display a dialog if any of the controls are 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 entering a contact here...
      End If
End Sub

But to make it a bit more complicated, each element should be checked individually, and if any of them is empty, its name() should change color to red:

Private Sub CommandButton_Add_Click()
      'Setting the name 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 nothing specified...
          Label_Last_Name.ForeColor = RGB(255, 0, 0) 'Set 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 entering a contact here...
      End If
End Sub

Inserting data

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

Dim row_number As Integer, salutation As String

"Selection of appeal
For Each salutation_button In Frame_Salutation.Controls
      If greeting_button.Value Then
          salutation = salutation_button.Caption 'Salutation selected
      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 into a 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 returned
OptionButton1.Value = True
  TextBox_Last_Name.Value = ""
  TextBox_First_Name.Value = ""
  TextBox_Address.Value = ""
  TextBox_Place.Value = ""
  ComboBox_Country.ListIndex = -1

General view

That's it, here you have the completed code for the exercise and the file to download:

Private Sub CommandButton_Close_Click()
      Unload Me
End Sub

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

Private Sub CommandButton_Add_Click()
      'Setting the name 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 nothing specified...
          Label_Last_Name.ForeColor = RGB(255, 0, 0) 'Set 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 filled, the values will be pasted into the worksheet
          Dim row_number As Integer, salutation As String
         
          "Selection of appeal
          For Each salutation_button In Frame_Salutation.Controls
              If greeting_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 on 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 inserting the data, we return 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 Excel file for download: controls_exercise2.xls

Articles on the topic: