[Lesson 14]<<[Table of Contents]>>[Lesson 16]
In a previous lesson, you have learned how to use the message box. In this lesson, you will learn how to create a dialog box using the UserForm in Excel 2010 VBA macro programming.
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 2010 VBA editor, select Insert then choose UserForm from the drop-down menu, as shown in Figure 15.1
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 controls such as text boxes, check boxes, command buttons, list boxes and more, as shown below:
Figure 15.2
Example 15.1
You can display information from the spreadsheet in a list box. If 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:
ColumnCount=2 ColumnHeads=True RowSource=Sheet1!a2:b5
* If you are using Excel 2016, you have to use ListFillRange instead of RowSource.
Press F5 to run the program and you can see the following output interface:
Figure 15.3
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 and the total are shown on the calculator as well as in cells c3.
The output interface is shown in Figure 15.4
Figure 15.4
