In this Lesson, we will examine...
UserForms
The Option Button Control
The Scroll Bar Control
The Frame Control
The RefEdit Control
The MultiPage Control
The List Box and Combo Box Controls
Using worksheet as a container
Working with user forms, for me is the most interesting and fun part of VBA application development. When designing the form, you need to know 3 things
b) How to place, resize and line up controls on a user form.
c) How to give controls properties in the Properties Window
1) Adding a Form to a Project
To add a form to a project, activate the VBE and select Insert/UserForm from the menu bar in the VBA IDE as shown in Figure 8.1 below…
2) Components of the UserForm Object
Just like you select a Worsheet properties, when you select a form, its properties appear in the Properties window in the VBA IDE (see Figure 8.1). Table 8.1 lists some of the properties of the UserForm object that you will commonly set at Design Time.
Selected Properties of the Userform Object
Table 8.1 - Click for larger image
Just as you have guess, the structure of a form code window or module is the same as any other module window. Click on the dropdown box on the upper-left corner will display all objects contained within the form, including the UserForm object. Here you will also find a general declarations section for making module level declarations in the form module.
Whereas the upper-right corner contains a dropdown list of all event procedures associated with the various objects that may be contained in the form. An example code window and properties for a form is shown in Figure 8.2.
Generally, the behavior of variables and procedures for UserForm are identical with other Excel objects we discussed before. The declaration statements Dim, Private, and Public keywords in a form module are identical to that of an object module as discussed in Part C and D. For example, the scope of variables and procedures declared as Public in the general declarations section of a form module are global just like any other the Excel objects discussed before.
The UserForm object has several event procedures, including Click(), Activate(), and
Initialize() among others. To view the full list of event procedures of the UserForm object, select the UserForm object in the object dropdown list and then select the event procedure dropdown list from the form module (see Figure 8.2). Some of these event procedures should be familiar, as they are common to several ActiveX controls. Table 8.2 lists a few of the more commonly used event procedures of the UserForm object.
Table 8.2 - Click for larger image
3) How To Add ActiveX Controls to a Form
Like the Worksheet object, the UserForm object is a container object. It is used to hold other objects. When a form is added to a project, the Control Toolbox should automatically appear (see Figure 8.1). If the Control Toolbox does not appear, select View/Toolbox from the menu bar.
You use the tools in the Control Toolbox to add controls to your UserForm. Just click the desired control in the Toolbox and drag it into the dialog box to create the control. After you add a control, you can move and resize it by using standard techniques.
When added to a form, you access the properties of an ActiveX control via the Properties window and you access event procedures associated with ActiveX controls via the form module that contains them. Table 8.3 shows the type of ActiveX control and what they do.
Table 8.3 - Click for larger image
Let’s do a simple exercise (Example A): A Label, Command button and TextBox are used.
i) From the VBE insert a form into a new VBA project.
ii) On the Properties Window, change its Caption to “Hello” (see Fig 8.3)
iii) Add a Label and CommandButton controls to the form and change their Name properties to something meaningful like lblOutput and cmdHello (see Fig 8.4 and Fig 8.5)
iv) Change caption to “Click Me” for CommanButton and delete the Label caption.
v) Add a TextBox and name it to txtEnter.
vi) Adjust the size and appearance of all the control to suit your taste.
vii) Next double click on the CommandButton control to activate its Code Window
viii) Select the Click() event on the upper-right dropdown list.
ix) Finally enter the code below to the Click() event procedure. (see Fig 8.6)
Example A:
Private Sub cmdHello_Click()
lblOutput.Caption = “Welcome ” & txtEnter.Text
End Sub
With the above procedure, user will enter his/her name in the textbox. After that if the user clicks the CommandButton control name cmdHello, the click event procedure is triggered, and the Caption property of the Label control named lblOutput is changed to “Welcome” follow by the name the user entered in the text box.(see Fig 8.7)
Fig 8.3 - Click for larger image
Fig 8.4 - Click for larger image
Fig 8.5 - Click for larger image
Fig 8.6- Click for larger image
Note that (see Figure 8.6) on the upper-left of the dropdown list on the control module list all the ActiveX controls added to the UserForm.
To test the application, select the form and click on Run/Sub UserForm on the VBE standard toolbar or menu bar, or press F5 on the keyboard. The form appears as a window above the Excel application. Click the Click Me button to output the simple message to the Label control. To close the form, simply click on the X in the upper-right corner of the window.
Fig 8.7- Click for larger image
I suggest you use these naming convention for all the controls:i) Label = lbl
ii) Textbox = txt
iii)Combobox = cbo
iv) ListBox = lb
v) CheckBox = chk
vi) OptionButton = opt
vii) RefEdit = rfe
viii) UserForm = frm
4) Showing and Hiding Forms
To load a form from the Excel application, use the Show() method of the UserForm object in a procedure that can be triggered from Excel VBA.The basic syntax follows:
UserFormName.Show [Modal]
For example, the following code displays a UserForm object in Example when the Click() event procedure of a Command Button control named cmdDisplayForm is triggered. The Command Button control can be added on a worksheet or another form.
Private Sub cmdDisplayForm_Click()
UserForm1.Show
End Sub
To hide a form from the user but retain programmatic control, usel the Hide() method of the UserForm object.
UserForm1.Hide
Note that the Hide() method does not remove the UserForm object from system memory, thus the form and its components can still be accessed programmatically. To remove a form completely from system memory, call VBA’s UnLoad() method.
UnLoad UserForm1
5) Designing Custom Dialog Boxes Using Forms
We have discussed in Example A, the ActiveX controls Label, TextBox and CommandButton to collect user input. As you can see, using the userform and the 3 controls has expand the capabilities of forms well beyond that of the InputBox() and MsgBox() functions.
There are a few more ActiveX controls that I am going to show you below.
a) The Frame Control
The function of the Frame control is to groups ActiveX controls on a form especially when the ActiveX controls grouped are related by content, or in the case of Option Button controls, be made mutually exclusive (see Fig 8.8).
The properties of the Frame control are seldom referenced in code. We usually set the Name and Caption properties along with a couple of appearance properties (Caption, Font, Height etc.) at Design Time.
You rarely access the Frame control programmatically. However, Frame control are often use to organizes or groups controls on a form for aesthetic appearance; in the case of Option Button controls, behavior.
b) The Option Button Control
Option Button controls are added to the userform as a group. It is common practice that we group all the Option Buttons with a Frame control. So no matter how many Option Button controls are added to the form only one can be selected at any given time.
When the user selects an Option Button from a group of Option Buttons, Excel VBA
will automatically deselect a previously selected Option Button. We will look at an example below to clarify this concept.
c) The RefEdit Control
RefEdit control is use to enable user select a range of cells from a worksheet for some specific task. Many Excel’s dialogs and wizards contain RefEdit controls, like the Goal Seek dialog box shown in Figure 8.9
Fig 8.9 - Click for larger image
When user select a range from an existing Excel worksheet, the textual reference for the selected range is automatically entered into the edit region of the control. You can also enter the range manually by typing in the text area of the control.
Let’s look at an example to illustrate the combination of using the RefEdit control, OptionButton, Label, Frame, CommandButton, TextBoxt controls.
Example B:
i. Insert a UserForm (see Figure 8.10)
Fig 8.10 - Click for larger image
ii. This dialog box has nine controls. Set the properties according to Table 8.4.
Table 8.4 - Click for larger image
The next set of steps makes executing this procedure an easy task: Double-click the Calculate button and enter the following Code:
Private Sub cmdCalc_Click()
Dim myRange As Range
Set myRange = Range(rfeInput.Text)
If optAverage.Value = True Then
lblResult.Caption = "Average ="
txtResult.Value = Round(Application.Average(myRange), 2)
ElseIf optMax.Value = True Then
lblResult.Caption = "Maximum ="
txtResult.Value = Application.Max(myRange)
ElseIf optMin.Value = True Then
lblResult.Caption = "Minimum ="
txtResult.Value = Application.Min(myRange)
End If
End Sub
Double-click the Close button and enter the following Code…
Private Sub CloseButton_Click()
Unload UserForm3
End Sub
iii. Activate a Excel worksheet and name it “Example_C”
iv. Fill a Range on the Worksheet with random numbers
v. Go back to VBE and select UserForm3
Double click on UserForm3, select the Initialize and enter the following code:
Private Sub UserForm_Initialize()
Worksheets("Example C").Activate
rfeInput.SetFocus
End Sub
The above code is to ensure the Worksheets("Example C") is active and the focus is on the RefEdit control.
vi. Run the program.(Run/Sub UserForm on the VBE)
First you select a range on the worksheet. The textual reference for the selected range is automatically entered into the edit region of the control. You can also enter the range manually by typing in the text area of the control.
After that you select which option you want to calculate by clicking on one of the option buttons. Finally you click on the Calculate button to start calculating. The label caption will change to “Average” if you’ve selected the Average option button and the result is stored in the textbox. (see Fig 8.11)
Close the program by clicking the close button.
d) The List Box and Combo Box Controls
The List Box control shows a certain number of values in a form where the user may select one or more items.
You can add the List Box control on the form with varying height and width such that it displays one or more items in the list. If there are more items in the list that can be displayed in the area provided, the scroll bars will automatically appear.
It is a good practice to draw the List Box control with its Height property set to a value large enough for several values to be displayed, because it is difficult to see the scroll bar when the control is at a minimum height.
If space on the form is an issue, use a Combo Box control instead and set the Style property to dropdown list. The Combo Box control combines the features of a List Box control with a Text Box control, allowing the user to enter a new value if desired and it also save you some space.
The difference between a combo boxes and the list boxes is that the combo box is a drop-down list and the user can submit a single value either one of the values from the drop-down list or any other value. The list box shows a certain number of values with or without a scroll bar and the user can select one or more values but not a value that is not in the list.
Properties of the List Box and Combo Box controls usually set at Design Time and Run Time are listed in Table 8. 5). Data is added to the List Box and Combo Box controls at run time using their AddItem() method.
ControlName.Additem (item)
Table 8.5 - Click for larger image
The AddItem() method must be called for every row of data added to the list. A looping code structure will often work well to complete this task (This will be demonstrated with Example below). Other methods belonging to both the List Box and Combo Box controls include, Clear() and RemoveItem() which remove all or one item from the control’s list, respectively.
Example C:
Add a UserForm and a ListBox named ListBox1 (shown below in Fig 8.12)
i. Select your UserForm and press F7 to activate the userform code window.
ii. Select the Initilize event procedure from the drop-down list at the top of the Code window.
iii. Add the initialization code for the form:
Private Sub UserForm_Initialize()
With ListBox1
.AddItem "Boston"
.AddItem "Chicago"
.AddItem "Detroit"
.AddItem "Los Angeles"
.AddItem "Miami"
.AddItem "New york"
.AddItem "Omaha"
.AddItem "Pasadena"
.AddItem "San Diego"
.AddItem "San Francisco"
.AddItem "Denver"
.AddItem "Florida"
End With
' Select the first list item
ListBox1.ListIndex = 0
End Sub
iv. To test the form, on the main menu of Visual Basic, click Run -> Run Sub/UserForm
To determine the selected item, add the following to the CommandButton1_Click procedure: The result is display in Figure 8.13
Private Sub CommandButton1_Click()
Dim Msg As String
Msg = "You selected item # "
Msg = Msg & ListBox1.ListIndex
Msg = Msg & vbNewLine
Msg = Msg & ListBox1.Value
MsgBox Msg
End Sub
Fig 8.13- Click for larger image
Note : the first item in a ListBox has a ListIndex of 0, not 1
Determining multiple selections
To enable the user select more than one item, you need to set the ListBox MultiSelect property to 1. To determine all selected items, you need to use the Selected property, which contains an array.
To allow multiple selections in a ListBox, set the MultiSelect property to
either 1 or 2. You can do so at design time by using the Properties window or
at run time by using adding VBA statement below to the UserForm_Initialize() event.
UserForm2.ListBox1.MultiSelect = 1.
On the VBE window, double click on CommandButton2 and enter the code below to the CommandButton2_Click event procedure:
Private Sub CommandButton2_Click ()
Dim i As Integer
UserForm2.ListBox1.MultiSelect = 2
Msg = “You selected” & vbNewLine
For i = 0 To ListBox1.ListCount – 1
If ListBox1.Selected(i) Then
Msg = Msg & ListBox1.List(i) & vbNewLine
End If
Next i
MsgBox Msg
End Sub
The message box below show the result.
To remove an item from the ListBox, you need to use the RemoveItem() statement. Add a CommandButton3 and enter the code below.
Private Sub CommandButton3_Click()
i = ListBox1.ListIndex
ListBox1.RemoveItem (i)
End Sub
The above code will enable the selected item on the ListBox to be deleted or remove.
The Combo Box control also includes a DropDown() method that, when invoked, displays the control’s list.
The most useful event procedure of the List Box and Combo Box controls is the Change() event. Although you may find the DropButtonClick() event procedure of the Combo Box control quite useful as well. The Change() event is triggered when the Value property of the control changes. (The Value property of the List Box and Combo Box control is the selected item from the list.) The DropButtonClick() event of the Combo Box control is triggered when the controls dropdown button is clicked signaling that the user is viewing the list of items in the control.
Be sure to check the Object Browser for a complete list of properties, methods, and events associated with the ActiveX controls discussed in this Part.
e) The MultiPage Control
The MultiPage control is another example of a container control that groups or organizes ActiveX controls. An example of the MultiPage control in the Excel application is the Options dialog box shown in Figure 8.14 which is quite familiar to you. As you can see from this example that the MultiPage control allows you to cram a lot of options onto a single form.
The SelectedItem property returns the currently selected Page object. It is useful for identifying what page on the MultiPage control is active. For example:
If MultiPage1.SelectedItem.Caption = “Page 1” Then
MsgBox “Page 1 is active.”
End If
Just like the other controls you can set and manipulate Multipage in your VBA code and also in Design Mode. Take note that there is no Activate() or Select() method of the MultiPage or Page objects.
Therefore, you need to set the Value property of the MultiPage control to an index value representing a specific Page object in the Pages collection object. The following statement selects the second page (index numbers start at zero) of a MultiPage control.
MultiPage1.Value = 1
f)The Scroll Bar Control
You have undoubtedly seen and used scroll bars in numerous applications for scrolling through lengthy documents or large figures. Scroll bars sometimes automatically appear on the sides and/or the bottom of VBA controls so the user can view the entire content displayed in a control. Situations such as these require nothing extra from you, or your program—the scroll bars are simply there to provide the user with a method of seeing the complete content of the control; however,
I believe you have used scroll bars many times as a method to see the complete contents of page or form. VBA also provides a Scroll Bar control that you can add to forms in your project to enhance an interface, so that the user can scroll through content on a form. Apart from this the scroll bar control also enable you to sets a value from a large range of choices. For example in the Excel Format Cells dialog box (see Figure 8.16), a scroll is related to a textbox for user to choose the number of decimal points.
To do this, you need to set certain properties of the scroll during Design time (Set the Min value and Max value in the properties window) and enter this code in the ScrollBar_Change event.
TextBox.Value = ScrollBar.Value
The Change() event procedure is triggered when the value of the Scroll Bar control is changed by the user i.e clicked.
Table 8.6 summarizes the major properties of the Scroll Bar control.
6) Using Worksheet as the Container or Control Panel
As promised earlier, I’ll now show you how to use worksheet as a container or control panel. Select a worksheet and activate the Control Toolbox by select View – Toolbars - Control Toolbox on the Excel menu. Then just drag the control object from the Control Toolbox into the worksheet. (You’ve done this on a UserForm…remember.
Example D: Controls use are Option Button, Frame, Label and TextBox. This time we insert all these control on a worksheet.
i) Select a worksheet and rename it to Compounding
ii) Insert 5 text boxes, 4 option buttons and a command button.
iii) Format the worksheet shown in Figure 8.17
iv) Rename all controls properties according to Table 8.7.
v) To do so, right click on the controls to activate their properties window.(see Fig 8. )
vi) Double click on the command button to activate the module window. Select the Click() event procedure and enter this code:
Private Sub cmdCalc_Click()
Dim Principal As Currency
Dim IntRate As Double
Dim IntEarned As Currency
Dim FutureValue As Currency
Dim RatePerPeriod As Double
Dim Periods As Integer
Dim CompoundType As Integer
Dim i As Double
Dim n As Intege
Principal = CCur(txtPrincipal.Text)
IntRate = CDbl(txtIntRate.Text) / 100
If optMonth.Value = True Then
CompoundType = 12
ElseIf optQuarter.Value = True Then
CompoundType = 4
ElseIf optSemi.Value = True Then
CompoundType = 2
Else
CompoundType = 1
End If
Periods = CInt(txtPeriods.Text)
i = IntRate / CompoundType
n = CompoundType * Periods
RatePerPeriod = IntRate / Periods
FutureValue = Principal * ((1 + i) ^ n)
IntEarned = FutureValue - Principal
txtIntEarned.Text = FormatCurrency(IntEarned)
txtAmtEarned.Text = FormatCurrency(FutureValue)
End Sub
Table 8.7 - Click for larger image
Fig 8.18- Click for larger image
First user has to enter the principal, interest rate and no. of period that need to be calculated. Next user can choose which compound frequency that apply by selecting one of the option buttons. Finally, click on the Calculate will start the procedure and the results will be displayed on the interest earned and amount earned text box. (see Figure 8.18)
(Download example file here)
Example E: The Controls used and embedded on the worksheet are a ComboBox and a SpinButton. This example will also show you, how you integrate controls and Excel built in function i.e =PMT() used to calculate the payment for a loan based on constant payments and a constant interest rate.
i) Select a worksheet, name it “Example E” and format it according to Figure 8.19.
ii) Insert a ComboBox and SpinButton.
iii) On Range(“C15”) insert this function: =PMT(C7/C11,C9*C11,-C5)
iv) On Range(“C17”) insert this formula: =SUM(C15*(C9*C11))
v) On Range(“C20”) insert this formula: =C17-C5
vi) Activate VBE by pressing ALT-F11, and double on the Worksheet(“Example E”) and select the Activate event on the right-hand dropdown list.
vii) Enter this code:
Private Sub Worksheet_Activate()
Me.ComboBox1.Clear
Me.ComboBox1.AddItem ("None")
Me.ComboBox1.AddItem ("Toyota Vios")
Me.ComboBox1.AddItem ("Honda City")
Me.ComboBox1.AddItem ("Toyota Camry")
Me.ComboBox1.AddItem ("Honda Accord")
End Sub
viii) Rightclick on SpinButton1 and select View Code.
Select the Change Event and enter this Code:
Range("C9").Value = Me.SpinButton1.Value
ix) Rightclick on SpinButton1 and select Properties
Enter the following:
Max = 10
Min = 1
SmallChange = 1
ix) Double click on ComboBox1 to bring up the Code window. Select the Change event and enter this code:
Private Sub ComboBox1_Change()
Dim loan As Single
Dim interest As Double
If Me.ComboBox1.Value = "Toyota Vios" Then
Range("C5").Value = 77000 'loan amount
Range("C7").Value = 0.045 'interest rate
ElseIf Me.ComboBox1.Value = "Honda City" Then
Range("C5").Value = 77500 'loan amount
Range("C7").Value = 0.055 'interest rate
ElseIf Me.ComboBox1.Value = "Toyota Camry" Then
Range("C5").Value = 150000 'loan amount
Range("C7").Value = 0.045 'interest rate
ElseIf Me.ComboBox1.Value = "Honda Accord" Then
Range("C5").Value = 145000 'loan amount
Range("C7").Value = 0.045 'interest rate
End If
End Sub
When Worksheet(“Example E”) is activated, the Activate event is triggered. This will load up the ComboBox1 with the type of cars.
When user select one of the car in the drop down list of the ComboBox1, the Change event is triggered. The code in this procedure will execute and calculation is made. You can choose the Length of Loan(years) by clicking the SpinButton and Range(“C9”) will be updated follow by the calculation as well.
(Download example file here)
7) Summary:
We have learn how to use VBA UserForms and worksheets as containers for our applications. We also discussed adding ActiveX controls to a form, including the Label, Command Button, Text Box, Frame, Scroll Bar, Option Button, RefEdit, MultiPage, Combo Box, and List Box controls and how to use the code window of a form.
