To create this Userform, execute the following steps:

1. Open the Visual Basic Editor. If the Project Explorer is not visible, click View, Project Explorer.

2. Click Insert, Userform. If the Toolbox does not appear automatically, click View, Toolbox. Your screen should be set up as below.

3. Add the label, text boxes (first at the top, the second below the first, and so on) and command button. Once this has been completed, the result should be consistent with the picture of the Userform shown earlier. For example, create a text box control by clicking on TextBox from the Toolbox. Next, you can drag a text box on the Userform.

4. To change the caption of the Userform, label and command button, click View, Properties Window and click on each control.

5. To show the Userform, place a command button on your worksheet and add the following code line:

Private Sub CommandButton1_Click()

UserForm1.Show vbModeless

End Sub

By adding vbModeless, you can use the Userform and work in your worksheet at the same time. We have now created the first part of the Userform. Although it looks neat already, nothing will happen yet when we click the command button on the Userform.

6. Open the Visual Basic Editor.

7. In the Project Explorer, double click on UserForm1.

8. Double click on the command button.

9. Add the following code lines:

Private Sub CommandButton1_Click()

Dim i As Integer

For i = 1 To 10
Controls(“TextBox” & i).Value = Cells(i + 1, 1).Value
Next i

End Sub

With just a few lines of code, we can fill the text boxes with the phone numbers from the sheet. We used the & operator to concatenate (join) the elements. These code lines work because we didn’t change the names of the text box controls (TextBox1, TextBox2, TextBox3, etc). To change the names of the controls, click View, Properties Window and click on each control.

Result when you click the command button on the sheet:

1. On the Developer tab, click Insert.

2. For example, in the ActiveX Controls group, click Command Button to insert a command button control.

3. Drag a command button on your worksheet.

4. Right click the command button (make sure Design Mode is selected).

5. Click View Code.

You can change the caption and name of a control by right clicking on the control (make sure Design Mode is selected) and then clicking on Properties. Change the caption of the command button to ‘Apply Blue Text Color’. For now, we will leave CommandButton1 as the name of the command button.

The Visual Basic Editor appears.

6. Add the code line shown below between Private Sub CommandButton1_Click() and End Sub.

7. Select the range B2:B4 and click the command button (make sure Design Mode is deselected).

Result:

 

To create this Userform, execute the following steps.

1. Open the Visual Basic Editor. If the Project Explorer is not visible, click View, Project Explorer.

2. Click Insert, Userform. If the Toolbox does not appear automatically, click View, Toolbox. Your screen should be set up as below.

3. Add the Multipage control, labels, text boxes (first at the top, the second below the first), frame, option buttons (first at the left, the second at the right), list box, Image control and command button. Once this has been completed, the result should be consistent with the empty version of the Userform shown earlier. For example, create a Multipage control by clicking on Multipage from the Toolbox. Next, you can drag a Multipage control on the Userform. When you arrive at the Gender frame, remember to draw this frame first before you place the two option buttons in it.

4. You can change the names and the captions of the controls. Names are used in the Excel VBA code. Captions are those that appear on your screen. It is good practice to change the names of the controls, but it is not necessary here because we only have a few controls in this example. To change the caption of the Userform, Multipage tabs, labels, frame, option buttons and command button, click View, Properties Window and click on each control.

5. To show the Userform, place a command button on your worksheet and add the following code line:

Private Sub CommandButton1_Click()

UserForm1.Show

End Sub

We are now going to create the Sub UserForm_Initialize. When you use the Show method for the Userform, this sub will automatically be executed.

6. Open the Visual Basic Editor.

7. In the Project Explorer, right click on UserForm1 and then click View Code.

8. Choose Userform from the left drop-down list. Choose Initialize from the right drop-down list.

9. Add the following code lines:

Private Sub UserForm_Initialize()

With ListBox1
.AddItem “Mountains”
.AddItem “Sunset”
.AddItem “Beach”
.AddItem “Winter”
End With

End Sub

The list box on page 2 will be filled. We have now created the first part of the Userform. Although it looks neat already, nothing will happen yet when we select an item from the list box or when we click on the OK button.

10. Download the images (right side of this page) and add them to “C:\test\”

11. In the Project Explorer, double click on UserForm1.

12. Double click on the list box.

13. Add the following code lines:

Private Sub ListBox1_Click()

If ListBox1.ListIndex = 0 Then
Image1.Picture = LoadPicture(“C:\test\Mountains.jpg”)
End If

If ListBox1.ListIndex = 1 Then
Image1.Picture = LoadPicture(“C:\test\Sunset.jpg”)
End If

If ListBox1.ListIndex = 2 Then
Image1.Picture = LoadPicture(“C:\test\Beach.jpg”)
End If

If ListBox1.ListIndex = 3 Then
Image1.Picture = LoadPicture(“C:\test\Winter.jpg”)
End If

End Sub

These code lines load a picture depending on the selected item in the list box.

14. Double click on the OK button.

15. Add the following code lines:

Private Sub CommandButton1_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 = TextBox1.Value
Cells(emptyRow, 2).Value = TextBox2.Value

If OptionButton1.Value = True Then
Cells(emptyRow, 3).Value = “Male”
Else
Cells(emptyRow, 3).Value = “Female”
End If

Cells(emptyRow, 4).Value = ListBox1.Value

‘Close Userform
Unload Me

End Sub

First, we activate Sheet1. Next, we determine emptyRow. The variable emptyRow is the first empty row and increases every time a record is added. Next, we transfer the information from the Userform to the specific columns of emptyRow. Finally, we close the Userform.

16. Exit the Visual Basic Editor, enter the labels shown below into row 1 and test the Userform.

Result:

1. On the Developer tab, click Insert.

2. In the ActiveX Controls group, click List Box.

3. Drag a list box on your worksheet.

Note: you can change the name of a control by right clicking on the control (make sure Design Mode is selected) and then clicking on Properties. For now, we will leave ListBox1 as the name of the list box. Create a Workbook Open Event. Code added to the Workbook Open Event will be executed by Excel VBA when you open the workbook.

4. Open the Visual Basic Editor.

5. Double click on This Workbook in the Project Explorer.

6. Choose Workbook from the left drop-down list and choose Open from the right drop-down list.

7. To add items to the list box, add the following code lines to the Workbook Open Event:

With Sheet1.ListBox1
.AddItem “Paris”
.AddItem “New York”
.AddItem “London”
End With

Use Sheet2 if your list box is located on the second worksheet, Sheet3 if your list box is located on the third worksheet, etc. If you use these code lines outside the Workbook Open event, you might want to add the following code line before these code lines. This code line clears the list box. This way your items won’t be added multiple times if you execute your code more than once.

ListBox1.Clear

8. To link this list box to a cell, right click on the list box (make sure design mode is selected) and click on Properties. Fill in D3 for LinkedCell.

Also see the ListFillRange property to fill a list box with a range of cells.

9. Save, close and reopen the Excel file.

Result:

Although in some situations it can be useful to directly place a list box on your worksheet, a list box is particularly useful when placed on a Userform.

1. On the Developer tab, click Insert.

2. In the ActiveX Controls group, click Combo Box.

3. Drag a combo box on your worksheet.

You can change the name of a control by right clicking on the control (make sure Design Mode is selected) and then clicking on Properties. For now, we will leave ComboBox1 as the name of the combo box.

Create a Workbook Open Event. Code added to the Workbook Open Event will be executed by Excel VBA when you open the workbook.

4. Open the Visual Basic Editor.

5. Double click on This Workbook in the Project Explorer.

6. Choose Workbook from the left drop-down list and choose Open from the right drop-down list.

7. To add items to the combo box, add the following code lines to the Workbook Open Event:

With Sheet1.ComboBox1
.AddItem “Paris”
.AddItem “New York”
.AddItem “London”
End With

Note: use Sheet2 if your combo box is located on the second worksheet, Sheet3 if your combo box is located on the third worksheet, etc. If you use these code lines outside the Workbook Open event, you might want to add the code lines below before these code lines. The first code line clears the combo box. This way your items won’t be added multiple times if you execute your code more than once. The second code line clears your own choice.

ComboBox1.Clear
ComboBox1.Value = “”

8. To link this combo box to a cell, right click on the combo box (make sure design mode is selected) and click on Properties. Fill in D2 for LinkedCell.

Also see the ListFillRange property to fill a combo box with a range of cells.

9. Save, close and reopen the Excel file.

Result:

Although in some situations it can be useful to directly place a combo box on your worksheet, a combo box is particularly useful when placed on a Userform.

1. On the Developer tab, click Insert.

2. In the ActiveX Controls group, click Check Box.

3. Drag a check box on your worksheet.

4. Right click the check box (make sure Design Mode is selected).

5. Click View Code.

Note: you can change the caption and name of a control by right clicking on the control (make sure Design Mode is selected) and then clicking on Properties. Change the caption of the check box to MyCheckBox. For now, we will leave CheckBox1 as the name of the check box.

6. Add the following code lines:

If CheckBox1.Value = True Then Range(“D2”).Value = 1
If CheckBox1.Value = False Then Range(“D2”).Value = 0

7. Click the check box on the sheet (make sure Design Mode is deselected).

Result:

Although in some situations it can be useful to directly place a check box on your worksheet, a check box is particularly useful when placed on a Userform.

1. On the Developer tab, click Insert.

2. In the ActiveX Controls group, click Option Button.

3. Drag two option buttons on your worksheet.

4. Right click the first option button (make sure Design Mode is selected).

5. Click View Code.

You can change the caption and name of a control by right clicking on the control (make sure Design Mode is selected) and then clicking on Properties. Change the captions of the option buttons to Female and Male. For now, we will leave OptionButton1 and OptionButton2 as the names of the option buttons.

6. Add the following code line:

If OptionButton1.Value = True Then Range(“D3”).Value = 10

7. Right click the second option button (make sure Design Mode is selected).

8. Click View Code.

9. Add the following code line:

If OptionButton2.Value = True Then Range(“D3”).Value = 20

10. Click the option buttons on the sheet (make sure Design Mode is deselected).

Result:

Although in some situations it can be useful to directly place option buttons on your worksheet, option buttons are particularly useful when placed on a Userform.

1. On the Developer tab, click Insert.

2. In the ActiveX Controls group, click Spin Button.

3. Drag a spin button on your worksheet.

4. Right click the spin button (make sure Design Mode is selected).

5. Click View Code.

You can change the name of a control by right clicking on the control (make sure Design Mode is selected) and then clicking on Properties. For now, we will leave SpinButton1 as the name of the spin button.

6. To link this spin button to a cell, add the following code line.

Range(“C3”).Value = SpinButton1.Value

7. You can set a maximum and minimum by adding the following code lines.

SpinButton1.Max = 100
SpinButton1.Min = 0

8. To change the incremental value, use the SmallChange property.

SpinButton1.SmallChange = 2

9. Click the arrows of the spin button (make sure Design Mode is deselected).

Result:

Instead of changing the properties of the spin button at runtime, you can also change the properties at design-time. To achieve this, right click on the spin button (make sure Design Mode is selected) and click on properties.

The instructions below do not teach you how to format the worksheet. We assume that you know how to change font types, insert rows and columns, add borders, change background colors, etc. Execute the following steps to create the loan calculator:

1. Add the two scrollbar controls. Click on Insert from the Developer tab and then click on Scroll Bar in the ActiveX Controls section.

2. Add the two option buttons. Click on Insert from the Developer tab and then click on Option Button in the ActiveX Controls section.

Change the following properties of the scrollbar controls (make sure Design Mode is selected).

3. Right mouse click on the first scrollbar control, and then click on Properties. Set Min to 0, Max to 20, SmallChange to 0 and LargeChange to 2.

4. Right mouse click on the second scrollbar control, and then click on Properties. Set Min to 5, Max to 30, SmallChange to 1, LargeChange to 5, and LinkedCell to F8.

When you click on the arrow, the scrollbar value goes up or down by SmallChange. When you click between the slider and the arrow, the scrollbar value goes up or down by LargeChange.

Create a Worksheet Change Event. Code added to the Worksheet Change Event will be executed by Excel VBA when you change a cell on a worksheet.

5. Open the Visual Basic Editor.

6. Double click on Sheet1 (Sheet1) in the Project Explorer.

7. Choose Worksheet from the left drop-down list and choose Change from the right drop-down list.

8. The Worksheet Change Event listens to all changes on Sheet1. We only want Excel VBA to run the Calculate sub if something changes in cell D4. To achieve this, add the following code line to the Worksheet Change Event (more about the Calculate sub later on).

If Target.Address = “$D$4” Then Application.Run “Calculate”

9. Get the right percentage in cell F6 (change the format of cell F6 to percentage). Right mouse click on the first scrollbar control, and then click on View Code. Add the following code lines:

Private Sub ScrollBar1_Change()

Range(“F6”).Value = ScrollBar1.Value / 100
Application.Run “Calculate”

End Sub

10. Right mouse click on the second scrollbar control, and then click on View Code. Add the following code line:

Private Sub ScrollBar2_Change()

Application.Run “Calculate”

End Sub

11. Right mouse click on the first option button control, and then click on View Code. Add the following code line:

Private Sub OptionButton1_Click()

If OptionButton1.Value = True Then Range(“C12”).Value = “Monthly Payment”
Application.Run “Calculate”

End Sub

12. Right mouse click on the second option button control, and then click on View Code. Add the following code line:

Private Sub OptionButton2_Click()

If OptionButton2.Value = True Then Range(“C12”).Value = “Yearly Payment”
Application.Run “Calculate”

End Sub

13. Time to create the sub. You can go through our Function and Sub chapter to learn more about subs. If you are in a hurry, simply place the sub named Calculate into a module (In the Visual Basic Editor, click Insert, Module).

Sub Calculate()

Dim loan As Long, rate As Double, nper As Integer

loan = Range(“D4”).Value
rate = Range(“F6”).Value
nper = Range(“F8”).Value

If Sheet1.OptionButton1.Value = True Then
rate = rate / 12
nper = nper * 12
End If

Range(“D12”).Value = -1 * WorksheetFunction.Pmt(rate, nper, loan)

End Sub

The sub gets the right parameters for the worksheet function Pmt. The Pmt function in Excel calculates the payments for a loan based on constant payments and a constant interest rate. If you make monthly payments (Sheet1.OptionButton1.Value = True), Excel VBA uses rate / 12 for rate and nper *12 for nper (total number of payments). The result is a negative number, because payments are considered a debit. Multiplying the result by -1 gives a positive result.

This chapter teaches you how to create an Excel VBA Userform. The Userform we are going to create looks as follows:

Add the Controls

To add the controls to the Userform, execute the following steps.

1. Open the Visual Basic Editor. If the Project Explorer is not visible, click View, Project Explorer.

2. Click Insert, Userform. If the Toolbox does not appear automatically, click View, Toolbox. Your screen should be set up as below.

3. Add the controls listed in the table below. Once this has been completed, the result should be consistent with the picture of the Userform shown earlier. For example, create a text box control by clicking on TextBox from the Toolbox. Next, you can drag a text box on the Userform. When you arrive at the Car frame, remember to draw this frame first before you place the two option buttons in it.

4. Change the names and captions of the controls according to the table below. Names are used in the Excel VBA code. Captions are those that appear on your screen. It is good practice to change the names of controls. This will make your code easier to read. To change the names and captions of the controls, click View, Properties Window and click on each control.

Control	Name	Caption
Userform	DinnerPlannerUserForm	Dinner Planner
Text Box	NameTextBox
Text Box	PhoneTextBox
List Box	CityListBox
Combo Box	DinnerComboBox
Check Box	DateCheckBox1	June 13th
Check Box	DateCheckBox2	June 20th
Check Box	DateCheckBox3	June 27th
Frame	CarFrame	Car
Option Button	CarOptionButton1	Yes
Option Button	CarOptionButton2	No
Text Box	MoneyTextBox
Spin Button	MoneySpinButton
Command Button	OKButton	OK
Command Button	ClearButton	Clear
Command Button	CancelButton	Cancel
7 Labels	No need to change	Name:, Phone Number:, etc.

A combo box is a drop-down list from where a user can select an item or fill in his/her own choice. Only one of the option buttons can be selected.

Show the Userform

To show the Userform, place a command button on your worksheet and add the following code line:

Private Sub CommandButton1_Click()

DinnerPlannerUserForm.Show

End Sub

We are now going to create the Sub UserForm_Initialize. When you use the Show method for the Userform, this sub will automatically be executed.

1. Open the Visual Basic Editor.

2. In the Project Explorer, right click on DinnerPlannerUserForm and then click View Code.

3. Choose Userform from the left drop-down list. Choose Initialize from the right drop-down list.

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 DataCheckBoxes
DateCheckBox1.Value = False
DateCheckBox2.Value = False
DateCheckBox3.Value = False

‘Set no car as default
CarOptionButton2.Value = True

‘Empty MoneyTextBox
MoneyTextBox.Value = “”

‘Set Focus on NameTextBox
NameTextBox.SetFocus

End Sub

Text boxes are emptied, list boxes and combo boxes are filled, check boxes are unchecked, etc.

Assign the Macros

We have now created the first part of the Userform. Although it looks neat already, nothing will happen yet when we click the command buttons on the Userform.

1. Open the Visual Basic Editor.

2. In the Project Explorer, double click on DinnerPlannerUserForm.

3. Double click on the Money spin button.

4. Add the following code line:

Private Sub MoneySpinButton_Change()

MoneyTextBox.Text = MoneySpinButton.Value

End Sub

This code line updates the text box when you use the spin button.

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 DateCheckBox1.Value = True Then Cells(emptyRow, 5).Value = DateCheckBox1.Caption

If DateCheckBox2.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & ” ” & DateCheckBox2.Caption

If DateCheckBox3.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & ” ” & DateCheckBox3.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

First, we activate Sheet1. Next, we determine emptyRow. The variable emptyRow is the first empty row and increases every time a record is added. Finally, we transfer the information from the Userform to the specific columns of emptyRow.

7. Double click on the Clear button.

8. Add the following code line:

Private Sub ClearButton_Click()

Call UserForm_Initialize

End Sub

Explanation: this code line calls the Sub UserForm_Initialize when you click on the Clear button.

9. Double click on the Cancel Button.

10. Add the following code line:

Private Sub CancelButton_Click()

Unload Me

End Sub

This code line closes the Userform when you click on the Cancel button.

Test the Userform

Exit the Visual Basic Editor, enter the labels shown below into row 1 and test the Userform.

Result: