Worksheet UserForm Interface

---

One of the great advantages of the Userform is its interaction with Excel spreadsheets, giving it dynamism and a differentiated interface.

In the example we are developing, related to the Userform Layout and the Userform Events, we still need to configure the command buttons and the UserForm spin button. We also need to establish a relationship between the data entered, the form and the worksheet.

---

Adding Events to Controls

Creating events for control buttons is similar to what was done for the UserForm_Inicialize().

---

Button click event

To create an event when there is a click (in the view mode) on the command button (Private SubCommandButton1_Click()):

1. Right-click the UserForm (Project Explorer)
2. Select View Code
3. In the left Dropdown menu select CommandButton
4. In the right Dropdown menu select Click

The VBE will present the following code:

Private Sub CommandButton1_Click()

End Sub

The CommandButton1 option in the left Dropdown only will be found if the command button () has already been previously added to your UserForm and its property (Name) has not been changed.

You must create events for each of the buttons on the UserForm.

In our example we will have three button events: Private SubCommandButton1_Click(), Private SubCommandButton2_Click() and Private SubCommandButton3_Click().

---

SpinButton text change event

To add functionality when the rotation bar is changed you need a specific Event Sub (Private SubSpinButton1_Change()):

1. Right-click the UserForm (Project Explorer)
2. Select View Code
3. In the left Dropdown menu select SpinButton
4. In the right Dropdown menu select Change

The VBE will present the following code:

Private Sub SpinButton1_Change()

End Sub

The SpinButton1 option in the left Dropdown menu will only be available if the spin button () has already been added to the Userform and its property (Name) has not been changed.

---

Configuring Control Events

We can add functionality to the events created by adding the following codes:

'Regarding the UserForm Register button
Private Sub CommandButton1_Click()

    Dim EmptyLine As Long

    'Finds the next empty line
    EmptyLine = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1

    'Adds the name value and the age value to columns "A" and "B" respectively
    Cells(EmptyLine, 1).Value = TextBox1.Value
    Cells(EmptyLine, 2).Value = TextBox2.Value

    'Identifies the selected option button and adds the gender to column "C"
    Select Case True

        Case OptionButton1
        Cells(EmptyLine, 3).Value = "Male"

        Case OptionButton2
        Cells(EmptyLine, 3).Value = "Female"

        Case OptionButton3
        Cells(EmptyLine, 3).Value = "Other"

    End Select

    'Adds the department value to column "D"
    Cells(EmptyLine, 4).Value = ComboBox1.Value

    'Adds the Desired Unit value to column "E"
    Cells(EmptyLine, 5).Value = ListBox1.Value

    'Identifies the selected checkboxes and adds them to column "F"
    If CheckBox1.Value = True Then
        Cells(EmptyLine, 6).Value = CheckBox1.Caption
    End If

    If CheckBox2.Value = True Then
        If Not IsEmpty(Cells(EmptyLine, 6).Value) Then
            Cells(EmptyLine, 6).Value = Cells(EmptyLine, 6).Value & "; " & CheckBox2.Caption
        Else
            Cells(EmptyLine, 6).Value = CheckBox2.Caption
        End If
    End If

End Sub

In the code above, EmptyLine dynamically determines the first empty line of "Sheet1" (from bottom to top).

With the Cells commands associated with the values of the controls we can save the Userform information in the worksheet.

---

'Regarding the UserForm Clear button
Private Sub CommandButton2_Click()

    Call UserForm_Initialize

End Sub

The above code will reload the UserForm into memory using the event Sub UserForm_Initialize(), resetting all selected options

---

'Regarding the UserForm Cancel button
Private Sub CommandButton3_Click()

    Unload UserForm1

End Sub

The above code will remove the UserForm from memory, closing it.

---

'Regarding the UserForm spin button
Private Sub SpinButton1_Change()

    TextBox2.Value = SpinButton1.Value

End Sub

The above code will add a feature that changes the displayed age in the text box as the user presses the spin button control.

---

Testing the UserForm

Go to the spreadsheet and enter in the first line the respective titles in the same way that the UserForm will insert the data

---

---

Consolidating Your Learning

Suggested Exercises

---

• Previous page
• Next page

---

• report error/feedback