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 theand the , 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.
Creating events for control buttons is similar to what was done for the UserForm_Inicialize().
To create an event when there is a click (in the view mode) on the command button (Private SubCommandButton1_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 can create these events by simply double-clicking the control on the Edit window of the UserForm.
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().
To add functionality when the rotation bar is changed you need a specific Event Sub (Private SubSpinButton1_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.
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.
Go to the spreadsheet and enter in the first line the respective titles in the same way that the UserForm will insert the data
SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.
Excel ® is a registered trademark of the Microsoft Corporation.
© 2021 SuperExcelVBA | ABOUT