Lesson 15 The UserForm

The UserForm let you create a dialog that allows the user to view data from the spreadsheet as well as perform some operations such as saving, printing, searching and more. To use the UserForm in the Excel VBA 365 editor, select Insert then choose UserForm from the drop-down menu, as shown in Figure 15.1

The UserForm is a blank form that you can place controls on it. It is accompanied by a Toolbox window that allows you to select controls to place on the form. The Toolbox comprises Active-X controls such as TextBox, CheckBox, CommandButton, ListBox and more, as shown below:

Example 15.1

You can display information from the spreadsheet in a Listbox on the UserForm. The following code display information from column 1 and column 2 from the spreadsheet with headings on the ListBox. ColumnCount is the property of the ListBox to display number of columns, ColumnHeads to display the heads, RowSource to define the range of cells.

Private Sub CommandButton1_Click()
 ListBox1.ColumnCount = 2
 ListBox1.ColumnHeads = True
 ListBox1.RowSource = "Sheet1!a2: b5"
End Sub

Example 15.2

You can create a simple calculator to perform a simple calculation involving the information on a spreadsheet.

Insert a Userform, then place two text boxes, one label control, and a command button onto the form. Set the ControlSource property of the first text box to sheet1!a1 and the ControlSource property of the second text box to sheet1!b1. Add three more labels, place the first label besides text box 1 and change the caption to number 1 and place the second label besides text box 2 and change the caption to number 2, and place the last label besides the label that you will display the sum of the two numbers in the text boxes and change to caption to Total. Lastly, change the caption of the command button to Calculate.

Now click on the command button and enter the following code:

Private Sub CommandButton1_Click()
 Label1.Caption = Val(TextBox1.Text) + Val(TextBox2.Text)
 Cells(1, 3).Value = Label1.Caption
End Sub

Press F5 to run the program and you will be presented the interface of a simple calculator. Click on the calculate button and you will obtain the sum of the two numbers from cells a1 and cells b1. The sum will be displayed in cells c3.