To use a UserForm, you need to launch the Excel VBA 2010 editor. In the Excel VBA 2010 editor, select Insert then choose UserForm from the drop-down menu, as shown in Figure 15.1
The UserForm is a blank form which you can place controls. It is accompanied by a Toolbox window which allows you to select controls to place on the form. The Toolbox comprises controls such as text boxes, check boxes, command buttons, list boxes and more, as shown below:
You can display information from the spreadsheet in a list box. To achieve that, insert a list box into the UserForm. Let’s say you wish to display information in column 1 and column 2 with headings from the spreadsheet, you need to set the following properties of the list box:
Press F5 to run the program and you can see the following output interface:
You can create a simple calculator to perform simple calculation involving the information on a spreadsheet.
Insert a Userform, then insert two text boxes, one label control and a command button. 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
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 and the total is shown on the calculator as well as in cells c3.
The output interface is shown in Figure 15.4