Excel VBA 2010 Lesson 15: UserForm

 [Lesson 14][Table of Contents][Lesson 16]

In this lesson, you will learn how to create a dialog box using the UserForm in Excel VBA 2010. In previous lessons, you have learned how to use the message box, which is a simple dialog box. However, to create more advance dialog boxes, you need to use 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 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




vba2010_figure 15.1

 

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:

vba2010_fig15.2

 

Figure 15.2

Example 15.1

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:

ColumnCount=2

ColumnHeads=True

RowSource=Sheet1!a2..b5




Press F5 to run the program and you can see the following output interface:

vba2010_fig15.3

Figure 15.3 




Example 15.2

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
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 is shown on the calculator as well as in cells c3.

The output interface is shown in Figure 15.4

vba2010_fig15.4

Figure 15.4 




 

 [Lesson 14][Table of Contents][Lesson 16]

 

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn