VBA Course: Controls

Controls are the elements (buttons, labels, text boxes, checkboxes, etc.) that can be inserted on a UserForm (or on an Excel sheet).

Controls also have a range of properties and events that vary from one control to another.

To begin, add a UserForm and insert the following 3 controls: a Label, a TextBox, and a CommandButton:

Modify the properties of the UserForm and the controls (including the (Name) properties for the name, the Caption property for the text, and the Font property for the text size) to achieve the following:

Currently, when entering a number and clicking the button, nothing happens.

To fix this, we will start by adding an event to enter the value from the text box into cell A1 and close the UserForm.

Double-clicking the button adds a default event to the UserForm's code. In this case, it is the desired event, but if needed, you can select a different event from the list:

The Click event is triggered when the button is clicked:

Private Sub CommandButton_submit_Click()

    'The value from the text box named "TextBox_number" is entered into cell A1 of the active sheet
    Range("A1") = TextBox_number.Value

    'Close (Unload) the UserForm (Me)
    Unload Me

End Sub

The value is then entered into cell A1 before closing the UserForm.

Next, we will add an event that is triggered when the value in the text box changes and it will modify the background color if the value is not numeric:

Private Sub TextBox_number_Change()

    If IsNumeric(TextBox_number.Value) Then 'If numeric value
        TextBox_number.BackColor = RGB(255, 255, 255) 'White
    Else 'Otherwise
        TextBox_number.BackColor = RGB(247, 205, 201) 'Light red
    End If

End Sub

The event is triggered whenever a character is entered or deleted in the text box.

Preview:

Lastly, we need to prevent form validation if the value is not numeric by adding an If statement:

Private Sub CommandButton_submit_Click()

    'If numeric value
    If IsNumeric(TextBox_number.Value) Then
        Range("A1") = TextBox_number.Value
        Unload Me
    End If

End Sub

File: userform1.xlsm

Checkboxes

Here is an example of using checkboxes to modify the values in column B based on the checkboxes checked in the UserForm:

The Click event of the button records the user's choices and then closes the UserForm:

Private Sub CommandButton_submit_Click()

    'Number 1
    If CheckBox1.Value = True Then 'If checked
        Range("B2") = "Yes"
    Else 'If unchecked
        Range("B2") = "No"
    End If

    'Number 2
    If CheckBox2.Value = True Then 'If checked
        Range("B3") = "Yes"
    Else 'If unchecked
        Range("B3") = "No"
    End If

    'Number 3
    If CheckBox3.Value = True Then 'If checked
        Range("B4") = "Yes"
    Else 'If unchecked
        Range("B4") = "No"
    End If

    'Close
    Unload Me

End Sub

Remember that a condition always checks if the test result is True:

If CheckBox1.Value = True Then 'If checked

So it is not necessary to add = True:

If CheckBox1.Value Then 'If checked

To further simplify the writing, the Value property is the default property for most controls, so it is not necessary to add it (as we saw earlier with the Value property of cells):

If CheckBox1.Value Then 'If checked

Adding .Value is optional:

If CheckBox1 Then 'If checked

After these simplifications, the code now looks like this:

Private Sub CommandButton_submit_Click()

    'Number 1
    If CheckBox1 Then 'If checked
        Range("B2") = "Yes"
    Else 'If unchecked
        Range("B2") = "No"
    End If

    'Number 2
    If CheckBox2 Then 'If checked
        Range("B3") = "Yes"
    Else 'If unchecked
        Range("B3") = "No"
    End If

    'Number 3
    If CheckBox3 Then 'If checked
        Range("B4") = "Yes"
    Else 'If unchecked
        Range("B4") = "No"
    End If

    'Close
    Unload Me

End Sub

Now, imagine that instead of 3 checkboxes, you have 30 checkboxes...

In that case, using a loop is more than welcome:

Private Sub CommandButton_submit_Click()

    Dim i As Integer

    'Loop through the checkboxes
    For i = 1 To 3
        If Controls("CheckBox" & i) Then 'If checked
            Range("B" & i + 1) = "Yes"
        Else 'If unchecked
            Range("B" & i + 1) = "No"
        End If
    Next

    'Close
    Unload Me

End Sub

In this example, the checkboxes are all unchecked when the UserForm opens.

To check the checkboxes based on the corresponding cell values being "Yes" when the UserForm is launched, add the UserForm_Initialize event and the following tests:

Private Sub UserForm_Initialize()

    If Range("B2") = "Yes" Then
        CheckBox1 = True
    End If
    
    If Range("B3") = "Yes" Then
        CheckBox2 = True
    End If
    
    If Range("B4") = "Yes" Then
        CheckBox3 = True
    End If
    
End Sub

To simplify this code, you have the option to write the If statement on a single line without End If when there is only one action to be performed:

Private Sub UserForm_Initialize()

    If Range("B2") = "Yes" Then CheckBox1 = True
    
    If Range("B3") = "Yes" Then CheckBox2 = True
    
    If Range("B4") = "Yes" Then CheckBox3 = True
    
End Sub

Using a loop is also possible:

Private Sub UserForm_Initialize()

    Dim i As Integer
    
    For i = 1 To 3
        If Range("B" & i + 1) = "Yes" Then Controls("CheckBox" & i) = True
    Next
    
End Sub

The file: userform2.xlsm