Userform in Excel VBA: Step-by-Step Tutorial

Userform in Excel VBA

In this chapter, you will learn how to design a UserForm in Excel VBA to collect and manage data. The UserForm we will create looks like the one below:

💎 Add the Controls

To include the controls in the  VBA UserForm in Excel, carry out the steps below.

1. Open the Visual Basic Editor. If the Project Explorer does not appear, go to the View menu and select Project Explorer from the list.

2. Click Insert, Userform. If the Toolbox is missing, go to the View menu and select Toolbox to make it visible on your screen. Your screen must appear exactly like the example shown below.

3. Make sure your UserForm contains all the controls provided in the table below. After completing this step, your result should look exactly like the UserForm shown earlier.

For example, to add a text box, simply click the TextBox option in the Toolbox panel. When you reach the Car frame, make sure to draw the frame first before adding the two option buttons.

4. Change the control names and captions to match the details provided in the table below. In Excel VBA, the name is used for programming purposes. The caption is the visible text shown on buttons, forms, or labels in the interface.

Name controls clearly to keep your project neat and organized. It becomes easier for others to read and understand the code. To edit the names and captions of the controls, click View, choose Properties Window, then select each control to update its properties.

Note: A combo box provides a list of options in a drop-down format, and the user can either select from the list or enter their own choice. Option buttons let users choose only one from several options.

💎 Show the Userform

You can display a UserForm by using a command button. To do this, add the following code to the command button:

Private Sub CommandButton1_Click()

DinnerPlannerUserForm.Show

End Sub

We will now create the UserForm_Initialize Sub procedure. This subroutine is executed automatically when the UserForm is displayed using the Show method.

1. Open the Visual Basic Editor.

2. In the Project Explorer window, right-click DinnerPlannerUserForm. Then, click View Code.

3. Choose Userform from the left drop-down list. Select the “Initialize” from the right drop-down menu.

4. Add the following code lines:

Private Sub UserForm_Initialize()

'Empty NameTextBox
NameTextBox.Value = ""

'Empty PhoneTextBox
PhoneTextBox.Value = ""

'Empty CityListBox
CityListBox.Clear

'Fill CityListBox
With CityListBox
.AddItem "San Francisco"
.AddItem "Oakland"
.AddItem "Richmond"
End With

'Empty DinnerComboBox
DinnerComboBox.Clear

'Fill DinnerComboBox
With DinnerComboBox
.AddItem "Italian"
.AddItem "Chinese"
.AddItem "Frites and Meat"
End With

'Uncheck DateCheckBoxes
Date1CheckBox.Value = False
Date2CheckBox.Value = False
Date3CheckBox.Value = False

'Set no car as default
CarOptionButton2.Value = True

'Empty MoneyTextBox
MoneyTextBox.Value = ""

'Set Focus on NameTextBox
NameTextBox.SetFocus

End Sub

Explanation: All text boxes are emptied, list and combo boxes are populated with data, and all check boxes are set to unchecked.

💎Assign the Macros

We have successfully built the first section of the UserForm. Although the design looks clean, the command buttons on the UserForm won’t do anything when clicked.

1. Open the Visual Basic Editor.

2. Double-click DinnerPlannerUserForm to open it.

3. Double-click the Money spin button.

4. Add the following code line:

Private Sub MoneySpinButton_Change()

MoneyTextBox.Text = MoneySpinButton.Value

End Sub

Explanation: This code updates the text box each time the spin button is clicked.

5. Double-click on the OK button.

6. Add the following code lines:

Private Sub OKButton_Click()

Dim emptyRow As Long

'Make Sheet1 active
Sheet1.Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
Cells(emptyRow, 1).Value = NameTextBox.Value
Cells(emptyRow, 2).Value = PhoneTextBox.Value
Cells(emptyRow, 3).Value = CityListBox.Value
Cells(emptyRow, 4).Value = DinnerComboBox.Value

If Date1CheckBox.Value = True Then Cells(emptyRow, 5).Value = 
Date1CheckBox.Caption

If Date2CheckBox.Value = True Then Cells(emptyRow, 5).Value = 
Cells(emptyRow, 5).Value & " " & Date2CheckBox.Caption

If Date3CheckBox.Value = True Then Cells(emptyRow, 5).Value = 
Cells(emptyRow, 5).Value & " " & Date3CheckBox.Caption

If CarOptionButton1.Value = True Then
Cells(emptyRow, 6).Value = "Yes"
Else
Cells(emptyRow, 6).Value = "No"
End If

Cells(emptyRow, 7).Value = MoneyTextBox.Value

End Sub

Explanation: First, we activate Sheet1. Next, we determine emptyRow. The variable named emptyRow identifies the next empty row and is updated every time a new record is inserted. Finally, data from the UserForm goes into the selected columns.

7. Double-click on the Clear button.

8. Add the following code line:

Private Sub ClearButton_Click()

Call UserForm_Initialize

End Sub

Explanation: Clicking the Clear button runs the UserForm_Initialize Sub, resetting the UserForm to default settings

9. Double-click on the Cancel Button.

10. Add the following code line:

Private Sub CancelButton_Click()

Unload Me

End Sub

Explanation: By clicking the Cancel button, this code instantly closes the UserForm window.

💎 Test the Userform

Close the Visual Basic Editor, type the labels in row 1, and test the UserForm to see its performance.

Result:

1/11 Completed! Learn much more about userforms ➝
Next Chapter: Range in Excel