Category: Buttons and Boxes

 [Lesson 21]<<[Table of Contents]>>[Lesson 23]

22.1 The CheckBox

The Checkbox and option button allows the user to select one or more items by checking the check box or check boxes concerned. For example, you may create a shopping cart where the user can click on checkboxes that correspond to the items they intend to buy can calculate the total payment.

One of the most important properties of the checkbox in Excel 2010 VBA  is Value. If the checkbox is selected or checked, the value is true, whilst if it is not selected or unchecked, the Value is False.

Example 22.1

In this example, the user can choose to display the sale volume of one type of fruits sold or total sale volume. The code is shown below:

Private Sub CommandButton1_Click()
If CheckBox1.Value = True And CheckBox2.Value = False Then
 MsgBox “Quantity of apple sold is” & Cells (2, 2).Value
ElseIf CheckBox2.Value = True And CheckBox1.Value = False Then
 MsgBox “Quantity of orange sold is ” & Cells(2, 3).Value
Else
 MsgBox “Quantity of Fruits sold is” & Cells (2, 4).Value
End If
End Sub

The output Interface is shown in Figure 22.1

Figure 22.1

22.2 Option Button

The option button control also lets the user selects one of the choices. However, two or more option buttons must work together because as one of the option buttons is selected, the other option button will be deselected. In fact, only one option button can be selected at one time. When an option button is selected, its value is set to “True” and when it is deselected; its value is set to “False”.

Example 22.2

This example demonstrates the usage of the option buttons. In this example, the Message box will display which option button selected by the user. The output interface is shown in Figure 22.2.

The code

Private Sub CommandButton1_Click()
If OptionButton1.Value = True Then
 MsgBox "Option1 is selected"
ElseIf OptionButton2.Value = True Then
 MsgBox "Option2 is selected"
Else
 MsgBox "Option3 is selected"
End If
End Sub

The output

Figure 22.2

 [Lesson 21]<<[Table of Contents]>>[Lesson 23]

 [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

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

<<Lesson 22>> [Contents] <<Lesson 24>>

We have learned how to work with check boxes, option buttons and text boxes in Excel VBA in the previous lesson. We shall continue to learn how to manipulate other Excel VBA controls in this lesson.

23.1 The ListBox

The function of the ListBox is to present a list of items where the user can click and select the items from the list. To add items to the list, we can use the AddItem method.

To clear all the items in the List Box, you can use the Clear method. The usage of Additem method and the Clear method is illustrated in Example 19.1 and The output is as shown in Figure 23.1

Example 23.1

Private Sub CommandButton1_Click()
For x = 1 To 10
ListBox1.AddItem “Apple”
Next
End Sub
Private Sub CommandButton2_Click()
For x = 1 To 10
ListBox1.Clear
Next
End Sub

Figure 23.1

23.2 ComboBox

The function of the ComboBox is also to present a list of items where the user can click and select the items from the list. However, the user needs to click on the small arrowhead on the right of the combo box to see the items which are presented in a drop-down list. In order to add items to the list, you can also use the AddItem method. The usage involving ComboBox is as shown in Example 19.2 and the output is as shown in Figure 19.2.

Example 23.2

Private Sub CommandButton1_Click()
ComboBox1.Text = “Apple”
For x = 1 To 10
ComboBox1.AddItem “Apple”
Next
End Sub
Private Sub CommandButton2_Click()
ComboBox1.Clear
End Sub

Figure 23.2

23.3 Toggle Button

Toggle button lets the user switches from one action to another alternatively. When the Toggle button is being depressed, the value is true and when it is not depressed, the value is false. By using the If and Else code structure, we can thus switch from one activity to another by pressing the toggle button repeatedly.

Example 23.3

In this example, the user can toggle between apple and orange as well as font colors.

Private Sub ToggleButton1_Click ()
If ToggleButton1.Value = True Then
Cells (1, 1) = “Apple”
Cells (1, 1).Font.Color = vbRed
Else
Cells (1, 1) = “Orange”
Cells (1, 1).Font.Color = vbBlue
End If
End Sub

View the animated image in Figure 23.3Figure 23.3

<<Lesson 22>> [Contents] <<Lesson 24>>

<<Lesson 21>> [Contents] <<Lesson 23>>

There are many Excel VBA controls that can be used to perform certain tasks by writing Excel VBA code for them. These controls are also known as Active-X controls. These controls are Excel VBA objects so they have their own properties, methods and events. They can be found on the Excel Control Toolbox, as shown in theFigure 17.1. We shall deal with the checkbox, the textbox and the option button.

Figure 22.1

22.1 The CheckBox

The Checkbox is a very useful control in Excel VBA. It allows the user to select one or more items by checking the check box or check boxes concerned. For example, you may create a shopping cart where the user can click on check boxes that correspond to the items they intend to buy, and the total payment can be computed at the same time.One of the most important properties of the checkbox is Value. If the check box is selected or checked, the value is true, whilst if it is not selected or unchecked, the Value is False.
The usage of the checkbox is illustrated in Example 22.1

Example 22.1

In this example, the user can choose to display the sale volume of one type of fruits sold or total sale volume. The code is shown below:

Private Sub CommandButton1_Click()

If CheckBox1.Value = True And CheckBox2.Value = False Then
MsgBox “Quantity of apple sold is” & Cells (2, 2).Value
ElseIf CheckBox2.Value = True And CheckBox1.Value = False Then
MsgBox “Quantity of orange sold is ” & Cells(2, 3).Value
Else
MsgBox “Quantity of Fruits sold is” & Cells (2, 4).Value
End If

End Sub

The Interface is shown in Figure 22.2

Figure 22.2

22.2 The TextBox

The TextBox is the standard Excel VBA control for accepting input from the user as well as to display the output. It can handle string (text) and numeric data but not images.

Example 22.2

In this example, we inserted two text boxes and display the sum of numbers entered into the two text boxes in a message box. The Val function is used to convert the string into numeric values because the text box treats the number entered as a string.

Private Sub CommandButton1_Click ()
Dim x As Variant, y As Variant, z As Variant
x = TextBox1.Text
y = TextBox2.Text
z = Val(x) + Val(y)
MsgBox “The Sum of ” & x & ” and ” & y & ” is ” & z
End Sub

Figure 17.3

22.3 The Option Button

The option button control also lets the user selects one of the choices. However, two or more option buttons must work together because as one of the option buttons is selected, the other option button will be deselected. In fact, only one option button can be selected at one time. When an option button is selected, its value is set to “True” and when it is deselected; its value is set to “False”.

Example 22.3

This example demonstrates the usage of the option buttons. In this example, the Message box will display the option button selected by the user. The output interface is shown in Figure 22.4.

The code

Private Sub OptionButton1_Click ()

MsgBox “Option 1 is selected”
End Sub
Private Sub OptionButton2_Click()
MsgBox “Option 2 is selected”
End Sub
Private Sub OptionButton3_Click()
MsgBox “Option 3 is selected”

End Sub

The Output Interface

Figure 22.4

<<Lesson 21>> [Contents] <<Lesson 23>>