Transcript EIN 4905/ESI 6912 Decision Support Systems Excel

Spreadsheet-Based Decision Support Systems
Chapter 18: User Interface
Prof. Name
Position
University Name
[email protected]
(123) 456-7890
Overview











18.1 Introduction
18.2 User Form Controls
18.3 User Form Options
18.4 Event Procedures
18.5 Variable Scope
18.6 Error Checking
18.7 Importing and Exporting Forms
18.8 Navigating
18.9 Professional Appearance
18.10 Applications
18.11 Summary
2
Introduction

Creating a user form with various controls

Setting the properties of these controls and work with common events

Coding in form modules using event procedures, variable scope, and
error checking

Creating a professional appearance for the workbook interface

Two user interface applications: one with controls on the spreadsheet
and one with several user forms
3
User Form Controls

Labels and Textboxes

Combo Boxes and List Boxes

Check Boxes, Option Buttons and Toggle Buttons

Command Buttons

Tab Strips and Multipage

Scroll Bar and Spin Buttons

Images and RefEdit
4
User Forms in Excel

Excel offers a simple tool to
cerate a User Form without
using VBA.
– Use Excel Options dialog
box to add the Form
command in Quick
Access toolbar.
– Select the range of data in
Excel.
– Click on the Form
command to create the
form displayed in the
figure.
5
User Forms in VBA

One of the best tools VBA for Excel offers to help in this communication
with the user is user forms.
– Insert > User Form from the menu in the VBE
– Insert icon as seen when inserting modules

You will then see a blank form and the Control Toolbox.

The Control Toolbox provides available controls which can be placed
on the form.
6
Figure 18.3

Each of the possible user form controls is labeled on this form.
7
Form Properties and Code

Create some code associated with the user
form which can capture the actions taken by
the user.

Use the Properties of these form controls to
name each control and understand the values
that can be assigned to them.

Use public variables to transfer actions
performed on the user form to the main code
of the program.
8
Form Properties and Code (cont’d)

The most important property which we will use for all user form items is
the Name property.

The name of a control from a user form is treated as an object in the
code.

We recommend that the beginning of your user form control name
identify which type of control it is. .

User forms will also have name property values.

For example, the name of a form may begin with “frm” followed by some
descriptive name.
– A form which gives the user some basic menu options might be called
“frmMenu”.
9
Form Properties and Code (cont’d)

If you are writing code associated with the user form which contains the
object you want to manipulate, you only have to enter the name of the
object directly and then use any property or method available.

To manipulate a user form object while in another user form code or in
any other module, type the name of the form first followed by a period
and the name of the object.
– frmMenu.lblQuantity
10
Figure 18.5


To view the code of our current user form, use the View Code button.
To view the form design of our current form code, use View Object
buttons.
11
Labels and Text Boxes

Labels and text boxes will be the most commonly used form items.

A label is used to:
– Give a description to any control on the form.
– Give general form description.
12
Labels

The name of a label should begin with “lbl” followed by some descriptive
name.

The only manipulation of labels that we developing applications is with
the Caption property and the Visible property.
– Caption property is used to change the text displayed in the label.
– Visible property takes True/False values.

For example, to hide the “lblQuantity” label, you would type:
– lblQuantity.Visible = False
13
Text Boxes

A text box is used to allow the user to enter some value. This value may
be a string or number.
– The label next to the text box should specify the kind of value the user should
enter.

The name given to a text box in the Properties window should begin with
“txt”.

Assign the value of a text box object to some variable in the code using
the Value property

For example, if there was a quantity variable in our code to which we
wanted to assign the “txtQuantity” text box value, we would type:
– quantity = txtQuantity.Value
14
Combo Boxes and List Boxes

Combo boxes and list boxes are used to give the user a list of options from
which they can select one item.

The main difference between combo boxes and list boxes is the way in which
the data is displayed.

A combo box will list the first entry next to a drop-down button.

When the button is pressed, all other items in the list are shown.

The user can also enter a value into the combo box as with a text box if they do
not wish to select and item from the list.
15
Combo Boxes

The name given to a combo box should begin with “cmb” followed by a
descriptive name.

There are several important properties associated with combo boxes.

The main property we use to capture the user’s selection is the Value
property.
– For example, if we have the variable usercontrol which is associated with the
user’s selection from the combo box of controls called “cmbControls”, we
could use the following code to assign a value to our variable:
 usercontrol = cmbControls.Value
16
Combo Boxes (cont’d)

To specify the values to be listed in the combo box, we use the
RowSource property.

This property can have a value equal to a specified range in a worksheet
or a range name.
– For example, if the range A5:B10 has five rows and two columns of data
which we want to show in the list box, we would set the RowSource property
as follows:
 Worksheets(“Sheet1”).Range(“A5:B10”).Name = “Options”
 cmbItems.RowSource = “Options”
17
Combo Boxes (cont’d)

Related to the RowSource property is the ControlSource property.

This property allows us to output directly to the spreadsheet the selection
made by the users.

The value of the ControlSource property must be a range name, just as
with the RowSource property.
– For example, to output the selection of the combo box cmbControls to the cell
A20, we would set the ControlSource property as follows:
 cmbControls.ControlSource = “A20”
18
Combo Boxes (cont’d)

If the RowSource of a combo box has more than one column, several
other properties can be used.

The first is the BoundColumn property.

This property determines which column of the multicolumn data will
contain the value which can be captured with the Value property.
– For the above example, if we set the BoundColumn to 1, then regardless of
what row of data is selected, only the data from column A will be stored in the
Value property.
 cmbControls.BoundColumn = 1
19
Combo Boxes (cont’d)

Another useful property for multicolumn data is ColumnCount.

ColumnCount is used to set how many columns of the RowSource data
should be displayed in the combo box.
– If this value is 0, no columns are displayed.
– If it is -1, all columns are displayed.
– We can use any other number less than 10 to display the corresponding
number of columns from the data. .

If you want to show non-adjacent columns which may not be at the
beginning of our data, use the ColumnWidths property.

If we set the column width of column A to 0, and set column B to some
non-zero width value, then only column B will be displayed.
– cmbControls.ColumnCount = 2
– cmbControls.ColumnWidths = “0”, “1”
20
Combo Boxes (cont’d)

Another useful property is the ColumnHeads property.
– This property can be set to True if there are column headings in the data
which you want to display in the combo box.

There are some formatting properties specific to combo boxes such as
ListRows and Style.

ListRows is used to specify the number of rows that should appear in the
combo box.

The Style property has two main options:
– fmStyleDropDownCombo option allows the user to enter data if a selection
from the combo box is not made.
– fmStyleDropDownList option does not.
21
Combo Boxes (cont’d)

Two other useful properties of the combo box are ListIndex and List.

The ListIndex property will return the index value of the control that was
selected (the index for combo boxes begins at 1; the index for list boxes
begins at 0).

The List property is used with the index of a control to select a particular
control from the list.
– For example, to initialize a combo box to show the first control in the list to be
selected, we would type:
 cmbControls.Value = cmbQuantity.List(1)
22
List Boxes

A list box has basically the same functionality and several similar
properties as a combo box.

A list box will list all items to be selected in a single box.
– There is no drop-down button as with combo boxes.

The user cannot enter a value into the list box.

The name given to a list box should begin with “lst” followed by a
descriptive name.
23
List Boxes (cont’d)

List boxes also use the RowSource, BoundColumn, ColumnCount,
ColumnWidths, ColumnHeads, ListRows, ListIndex, and List properties
as described with combo boxes.

The list box has a unique property called MultiSelect.

The MultiSelect property determines if users can select more than one
control from the list box.
– fmMultiSelectSingle: Select only one control at a time.
– fmMultiSelectMulti : Select more than one control by clicking on several
from the list.
– fmMultiSelectExtended : Select a section of adjacent or non-adjacent
controls from the list box by pressing Shift or CTRL keys respectively.
24
Check Boxes, Option Buttons, and Frames

Check boxes and option buttons are used to create Boolean selection
options for the user.

Frames can be used to group these items or other related items.

Check boxes imply a positive selection when checked.
– That is a yes, true, on, etc value.
– The opposite is true if they are unchecked.
25
Check Boxes

The name given to a check box should begin with “chk”.

The Value property of check boxes can be used as with Boolean
variables.
If chkAuthor.Value = True Then
actions…
End If
--------------------------------------If chkAuthor Then
actions…
End If

We also use the Caption property to give a brief description to each
check box.
26
Option Buttons

Option buttons imply a positive selection when selected.
– That is a yes, true, on, etc value.
– The opposite is true if they are de-selected.

The name given to an option button should begin with “opt”.
27
Option Buttons (cont’d)

The Value property of option buttons can be used as with Boolean
variables.
If optBuy.Value = True Then
actions…
End If
--------------------------------------If optBuy Then
actions…
End If

We also use the Caption property to give a brief description to each
option button.

We can also use option buttons with a frame.
28
Frames

A frame groups similar items together.
– For example, it can be used to group text boxes which contain related data or
check boxes which apply to the same category.

The name of a frame should begin with “fra” followed by a descriptive
name.
29
Frames (cont’d)

Frames primarily use the Caption property and Visible property.

Frames become more interesting when applied to option buttons as this
makes the option buttons mutually exclusive.
– When option buttons are used without a frame, more than one can be
selected (as with check boxes).
– When option buttons are placed inside a frame, only one can be selected at a
time.

This feature is only true for option buttons (not for check boxes, or toggle
buttons).
30
Toggle Buttons

Toggle buttons are similar to check boxes and option buttons in that
they imply a positive selection when clicked.
– That is a yes, true, on, etc value.
– The opposite is true if they are un-clicked.

A toggle button name should begin with “tgl” followed by a descriptive
name.
31
Toggle Buttons (cont’d)

Toggle buttons also have Values similar to Boolean variables.
If tglYear1.Value = True Then
actions…
End If
------------------------------------If tglYear1 Then
actions…….
End If

Toggle buttons also use the Caption property to give a brief description
of what the toggle button will select.
32
Command Buttons

Command buttons, are used for their associated event procedures
more than for their properties.

The only property we will use often with this control (aside from Visible
possibly) is the Caption property.

Command buttons should be named starting with “cmd”.

The two command buttons we will use most often will be called “cmdOK”
and “cmdCancel”.
33
Tab Strips and Multi Page

Tab strips and multi page items allow you to organize user input
options.

Tab strips group data into different sections of this one control.
– All sections or tabs have the same layout.
– One tab strip will have a set of controls which will appear on each tab.
– Each tab can be associated with a group of data to which the tab strip
information belongs.
34
Tab Strips

A tab strip should be named starting with “tab”.

Since a tab strip is a collection of tab objects, different properties of
tabs can be considered sub properties of tab strip.
– tabCustomers.Tab(customer1)

The main property used with tab strips to capture a selected value is
SelectedControl.

We can find the number of tabs in the selected tab strip by using the
Count method.

To add a new tab to the tab strip, just right-click on the tab strip in the
design view of the user form in the VBE and choose “new page” from
the list of options.
35
Figure 18.14

The tabs have the same textbox control, but different values are shown
for different tabs.
36
Figure 18.15

To change the value of the textbox, we need to determine which tab the
users have selected.

We use the Change event procedure of the tab strip and a Select Case
statement to check the value of the tab strip.
37
Multi Page

A multi page control can be considered a collection of individual form
objects, or pages.
– Each page can have a different layout and is treated as a separate form.

A multi page should be named starting with “mpg”.

The Value property is used with multi pages to denote the index of the
particular page.
38
Figure 18.17

Each page of a multipage has a unique set of controls.
39
Scroll Bar and Spin Buttons

Scroll bars and spin buttons are both used to update values of other
controls using event procedures.

Scroll bars can change text, numerical values, or other values of
associated controls.

A scroll bar should be named starting with “scr”.
40
Scroll Bars

The value of the scroll bar, which is numerical, can be captured using the
Value property.

There are also the Min and Max properties which can be set for the scroll
bar values, along with a SmallChange and LargeChange property to
specify how much the scroll bar value should change for each change in
the scroll bar position.

There is also an Orientation property which can be used to set the scroll
bar position to vertical or horizontal.
41
Spin Buttons

Spin buttons are similar to scroll bars, except that they only increment
or decrement numerical values of other controls

A spin button name should start with “spn”.

We also use the Value property and Min, Max, and SmallChange
properties.

We can also apply the Orientation property to spin buttons.
42
Images

Images allow you to display a picture in the form.

An image can be named starting with “img” and the picture name.

To assign an image to the image window, use the Picture property and
Browse options.

We can use the PictureAlignment and PictureSizeMode properties to
adjust the alignment and size of our image.
43
RefEdit


RefEdit is used to select or enter a worksheet range.
There is a button, similar to what is used in Excel dialog boxes, which
allows the user to collapse the form while they select a range directly
from the worksheet.

The name for RefEdit should begin with “ref”.

To capture the range the user has selected, use the Value property.
– This value will be a string variable type and so can only be assigned to a
string variable.
44
User Form Options

There are some properties associated with the user form itself which can
be useful.

There are several Position properties that can be modified.
– To view these, use the Categorized tab of the Properties Window and scroll
to the Position category.
– The values of these positions are relative to the left and top margins of the
worksheet and are measured with Excel’s point system.
– To align several controls on the form, we can select and right-click and
choose Align from the list of drop-down options.
45
User Form Options (cont’d)

Another property of the user form is ShowModal.
– When set to True, the form must be completed before the user can return to
the program or use any worksheet.
– When this property is set to False, the form becomes modeless, or a floating
form.
– When the user form is modeless, actions can occur on the worksheet and the
program can continue while the form is still showing.
– This can be useful for navigational purposes or to have a constant set of
options always available to the user.
46
User Form Options (cont’d)

There are some general properties that apply to the user form as well as
to most controls.

These include general formatting properties such as
– Font
– BackColor
– ForeColor

As we have mentioned already some other common properties found
for most if not all controls are Name, Caption, and Visible.

There are three other important common properties used with most/all
controls, they are
– TabIndex
– TabStop
– Locked
47
User Form Options (cont’d)

TabIndex and TabStop control the tabbing order in which the user can
move through controls on the user form with the Tab button of their
keyboard.
– The TabIndex property takes a numerical value from 1 to the number of
controls on the user form; 1 being the first item visited when Tab is pressed.
– The TabStop property can be set to True or False depending on whether or
not you want the user to be able to select a certain control.
48
User Form Options (cont’d)

The Locked property takes True or False values and allows you to
prevent the user from entering or changing any values of a control.
– The control will appear darkened and its value, if any, will be displayed, but
the user will not be able to modify the control value if the Locked property is
set to True.

The ControlTipText property allows us to enter some text to serve as
brief instructions to the users to explain what a particular control will do.
– This text will appear when users bring their cursor over the control.
– This property is available on all form controls.
49
Event Procedures

Event procedures are code procedures which are associated with an
event or action that occurs on a control.

There are many event procedures that can be associated with the action
of the user form controls.
– To find the unique list of events for each control, we simply select the name
of the control from the upper-left drop-down list of the code view of a
particular form.
– Once we have selected a control, the drop-down list in the upper right-hand
corner of the code window will have a list of events, or actions, associated
with that particular control.

We will review a few of the more commonly used event procedures.
50
Figures 18.22 and 18.23

Example of selecting an event procedure for an option button.
51
Event Procedures (cont’d)

A commonly used event procedure is the Initialize procedure of the user
form.
– This procedure will run when the form is first opened.

The main event procedure associated with command buttons is the Click
event.

Use the Show method of the form object to open the named form.

Use the Unload Me statement to close the current form.
52
Event Procedures (cont’d)
Sub cmdOK_Click()
quantity = txtQuantity.Value
With fraBuySell
If .optBuyThen
actions1…
ElseIf .optSell Then
actions2….
End If
End With
Unload Me
frmMenu.Show
End Sub
---------------------------------------------Sub cmdCancel_Click()
Unload Me
End
End Sub
53
Event Procedures (cont’d)

We most commonly use the Change, or Scroll, event procedures to
associate the change in the scroll bar values with an effect on another
control value.
Sub scrSize_Change()
scrSize.Min = 1
scrSize.Max = 30
scrSize.SmallChange = 1
Select Case scrSize.Value
Case 1 to 5
txtSize.Value
Case 6 to 15
txtSize.Value
Case 16 to 25
txtSize.Value
Case 26 to 30
txtSize.Value
End Select
End Sub
= “too small”
= “small range, but good”
= “large range, but good”
= “too large”
54
Event Procedures (cont’d)

Spin buttons also use the Change event procedure primarily to associate
its values with the values of another control.
Sub spnHeight_Change()
spnHeight.Min = 4.5
spnHeightMax = 7.0
spnHeight.SmallChange = 0.25
txtHeight.Value = spnHeight.Value
End Sub

Another more common event procedure is the After Update procedure.

We can use this with textboxes to perform some actions immediately
after the users enter a value.
Sub txtDate_AfterUpdate()
txtDay.Value = FindDay(txtDate.Value)
End Sub
55
Event Procedures (cont’d)
Form Object
Common Events
User Form
Initialize
Command Button
Click
Scroll Bar
Change, Scroll
Spin Button
Change
Textbox
After Update
Tab Strip
Change
Multipage
Change
Options Buttons
Click, Change and Check Boxes
56
Variable Scope

Any variable declared within a form module can only be used directly in
the procedures for that particular form.

Even if a variable is declared as a public variable within a form module, it
is public only in that module.

Consider the following code within the frmMain module.
Public UserInput As Integer
Sub cmdOK_Click()
UserInput = txtInput.Value
Unload Me
End Sub
57
Variable Scope (cont’d)

Even though the integer variable UserInput has been declared as a
public variable in the form module, it can not be referred to directly in
any other module.

If we wanted to use the input value given by the user in the txtInput
textbox on the form in another module in the application, we could not
use the UserInput variable as is.

In order to refer to a public form variable outside of the form module,
you have to provide the name of the form before the variable name.
– frmMain.UserInput

In the same way, we can use the form name to refer to any of its
controls outside of the form module
– frmMain.lblQuantity.Visible = True
58
Variable Scope (cont’d)

However, it is important to keep in mind that all form control values are
reset when the Unload Me statement is used.

If you are intending to refer to a control value in a later procedure in
another module, you must call that procedure before making the Unload
Me statement in the form code.
Sub cmdOK_Click()
‘txtInput.Value has been entered by user
Call Calculations
Unload Me
End Sub
59
Variable Scope (cont’d)

The Unload Me statement is also used to close the form.
– We do not want to bring the user through the rest of the application with the
initial input form still displayed.

We generally recommend assigning form control values to variables
defined as public variables outside of the form module.
– This allows us to make the Unload Me statement without losing the variable
values.
Sub cmdOK_Click()
UserInput = txtInput.Value
Unload Me
Call Calculations
End Sub
60
Error Checking

Any time we are receiving something from the user, we have to check for
errors.

An important check with forms is if the user entered values for multiple
controls.
– We use the following For, Next loop to perform this check.
Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = “TextBox” Then
If ctl.Value = “” or IsNumeric(ctl) = False Then
MsgBox “Please enter a numeric value in all textboxes.”
ctl.SetFocus
End Sub
End If
End If
Next
61
Error Checking (cont’d)

The Control data type is another example of an Object data type.

The For Each, Next loop checks all of the controls in our form.

Me.Controls refers to the set of controls in the current form.

To determine the type of control, we use the TypeName function with an
If,Then statement.

The statement ctl.SetFocus selects the control we found to cause an
error so that the user’s cursor is in the first place where an error was
found.
62
Importing and Exporting Forms

Any form you create can be exported, or saved as a template.

To save a form as a template, right-click on the form name in the Project
Explorer and choose Export File form the list of options.

To use a form template, or import a form, you can again select any form
from the Project Explorer window and choose Import File from the list
of options.
– You can also go to File > Import File at any time.
63
Figure 18.24

A standard form template named frmBasic may be imported several
times within the same application.
64
Navigating

Buttons are usually a great tool for navigating the user not only through
different sheets of the program, but also through different steps or
actions.
– Some of these buttons might be labeled “Next”, “Back”, “Exit”, “Start”, or
“View Example”.

They should be placed at the top of the worksheet in a visible spot.

The code for these macros can be identical or call an identical function.
65
Navigating (cont’d)

A useful property of these buttons, or shape objects, is the Visible
property.

By naming these buttons appropriately, you can use the Shape object
and Visible property to hide and show different buttons.

This is a good way to prevent your user from being confused or
overwhelmed by too many options.
66
Professional Appearance

Protecting the Worksheet

Sheet Options
67
Protecting the Worksheet

To prevent your user from changing formatting or title values in any
worksheet tables or other features, you can protect the worksheet.

Use the Locked property of a range of cells.
– For any cells which the user will need to enter values, or for which the
program may enter values, leave the Locked property at its False default
value.
– Set all other cells’ Locked properties to True.
– Any cell which is locked cannot be modified.
68
Protecting the Worksheet (cont’d)

After the appropriate cells have been locked and unlocked, select Home
> Cells > Format command from the Ribbon.

From the drop-down list, select Protect Sheet option.

You will be prompted to enter a protection password and then you can
set the specific options which the user will no longer be able to perform
with locked cells.
69
Protecting the Worksheet (cont’d)

For example, if a particular input range is locked, but we want to input
new data via some code (using input either from an Input Box or user
form), we might type the following:
With Range(“Input”)
.Locked = False
.Value = UserInput
.Locked = True
End With

Another way to prevent users from modifying cell values is to hide rows
or columns in our worksheet. We can do this using the Hidden property;
this property takes the values True or False.
70
Sheet Options

There are some other options which can be set for each worksheet in
your program to finalize the version which the user will see.

To view these options, select the View tab from the Ribbon.

Some of the options we usually change include
– Not viewing gridlines
– Hiding the formula bar
– Hiding column and row headings

The View > Workbook Views > Full Screen command when active,
hides the Ribbon.
71
Applications

Real Estate Search Spreadsheet Application

Product Search Form
72
Real Estate Description

Consider a real estate search application in which a user can search for
houses based on certain criteria.

This search is performed on an Excel database of real estate data.

The criteria for this search includes a maximum price, minimum area
(square feet), minimum number of bedrooms, minimum number of
bathrooms, and location in the city.
73
Figure 18.25
74
Figure 18.26
75
Max Price Criteria

We use a scroll bar to determine the max price criteria.

We name the control: scrPrice.

We set the Max value at 200,000.

We set the Min value at 75,000.

We set the SmallChange and LargeChange values both to 1,000.
76
Max Price Criteria (cont’d)

The LinkedCell property holds the value of
a cell name in which the value of the control
will be displayed.

In this application, we set the LinkedCell
property of the scroll bar to B6.

We then format this cell as currency so that
whenever the scroll bar value changes, the
price is shown in the cell.
77
Min Area Criteria

We then have a textbox for the user to specify the minimum area criteria.

We name the control, txtArea, and do not set any other property values.
78
Min Number of Bedrooms and Bathrooms Criteria

These two controls are spin buttons.

We name them spnBed and spnBath respectively.

The most important properties for spin buttons are Max, Min, and
SmallChange.
– For spnBed, we set these equal to 5, 1, and 1 respectively.
– For spnBath we set these equal to 3, 1, and 1 respectively.

We will also use the LinkedCell property for both of these controls in
order to display the values of the spin buttons.
– For spnBed, we set the LinkedCell property to B10.
– For spnBath, we set the LinkedCell property to B12.
79
Location Criteria

We use a combo box to specify the location criteria of the house.

We name this combo box as cmbLocation.

When using a combo box or list box on the spreadsheet, the RowSource
property is called the ListFillRange.
– In cells I5:I8, we list the names of the four regions, or locations, where
houses can be found: Northwest, Southwest, Northeast, and Southeast.
– We also have All listed as an option to view all locations.
– We name this range “Location”.
80
Figure 18.28
81
Command Buttons

We first create a “Search” button, named cmdSearch, which we will use
to capture the current values of all the controls as search criteria.

We then create a “View All” button, named cmdReset, which we will use
to reset any filtering done and show the original database.
82
Filtering

We will use the Click event procedure of the cmdSearch button to filter
the house information based on the criteria specified.

In order to filter the house data, we will be using the AutoFilter method.

Since we will always be filtering the same range of data, which is the
original Excel database, we have named this range “Houses” for easier
reference.
83
Filtering (cont’d)

For the price and area criteria, we will also be using a custom filter since
we are not searching based on known values from the data, but rather
from user-provided values.
Range(“Houses”).AutoFilter Field:=(number), Criteria:= “(inequality)” (value),
Operator:=xlAnd

The field numbers we will use are based on the following matching:
–
–
–
–
–
–
–
Address = 1
Agent = 2
Price = 3
Area = 4
Bedrooms = 5
Bathrooms = 6
Location = 7
84
Filtering (cont’d)

For the price criteria, the inequality will be “<=”.

For the area criteria, the inequality will be “>=”.

For the number of bedrooms and number of bathrooms, the inequality
will also be “>=”.

For the location criteria, we can ignore the inequality and just give the
value (we can also ignore the Operator argument).

The values for each criteria will be the value of the control which is
involved in the criteria.
85
Figure 18.29

The “Search” button procedures
86
“View All” Button Event Procedure

For this button we want to remove all filtering from the data.

To remove any filtering, we just simply type the AutoFilter method without
any fields or criteria:
Range(“Houses”).AutoFilter
87
Show All Procedure

We notice that whatever filtering is done first, remains as multiple criteria
are specified.

Therefore, before each criteria’s filtering is done, we must first reset our
data to be unfiltered.

We create a small procedure to call multiple times.
88
Worksheet Activate Event Procedure

This procedure could be used to call the ShowAll function to ensure that
the entire house data is displayed when the user first opens the
application.

We can also ensure that the row source of the combo box is initialized;
again, we use the ListFillRange property of the spreadsheet combo box
to do this.
89
Application Conclusion

The application is now complete.

Check the functionality of the program.
90
Product Search Description

In this application, we are designing a search form for a product in a
computer product database.

The program finds a product based on certain search priorities and
search criteria.
91
Preparation

There are only two sheets needed for this application
– “Products Search”
– “Products Database”

The “Products Search” sheet is the welcome sheet.
– A brief description of he application is given, and there is a Start button.

The “Products Database” sheet contains the data through which we will
search for products which meet the users criteria.
– The user, however, will never see this sheet .

After setting up these sheets some ranges will be named as further
preparation.
92
Figure 18.33
93
Figure 18.34
94
User Forms

There will be two main forms used in this application
– “Search Priority”
– “Search Criteria”

The “Search Priority” form will determine if the user wants to find their
product based on lowest price or quality level.
– This form will use a frame with two option buttons to give the user these
options.
– A description label is given.
– The typical two command buttons are used.
 OK and Cancel
95
Figure 18.35
96
Figure 18.36
97
User Forms (cont’d)

The second form will take more input from the user to determine which
product they are searching for and what other criteria they are searching
by.
– This form uses frames to group some controls together.
– We name the OK button on this form cmdFindProduct.

Some of these labels and frames may not appear depending on the
users choice in the search priority form.
98
Figures 18.37 and 18.38
99
“Search Criteria” Code
Private Sub UserForm_Initialize()
cmbProducts.RowSource = "ProdList"
chkStandard = True
chkPremium = True
txtPriceLimit.Value = 100
End Sub
----------------------------------------------------------Private Sub spnPriceLimit_Change()
txtPriceLimit = spnPriceLimit.Value
End Sub
-----------------------------------------------------------
100
“Search Criteria” Code (cont’d)
Private Sub cmdFindProduct_Click()
Product = cmbProducts.Value
If QualityLevel Then
If chkStandard And chkPremium Then
Quality = "Any"
ElseIf chkStandard Then
Quality = "Standard"
ElseIf chkPremium Then
Quality = "Premium"
End If
End If
If IsNull(txtPriceLimit.Value) = False And txtPriceLimit.Value > 0 Then
BestPrice = txtPriceLimit.Value
Else
BestPrice = 0
End If
Unload Me
Call FindProduct
End Sub
101
FindProduct Procedure
i=1
RowStart = 0
RowEnd = 0
If Price Then
'sort by product and then by price
Range("Database").Sort Key1:=Range("Product"), Order1:=xlAscending,
key2:=Range("Price"), order2:=xlAscending
With Range("Database")
Do While .Cells(i, 1) <> ""
If .Cells(i, 1) = Product Then
RowStart = i
Do While .Cells(i, 1).Value = Product
If .Cells(i, 6).Value > BestPrice Then
RowEnd = i - 1
Exit Do
End If
i=i+1
Loop
RowEnd = i - 1
Exit Do
End If
i=i+1
Loop
End With
102
FindProduct Procedure (cont’d)
ElseIf QualityLevel Then
'sort by product and then by quality
Range("Database").Sort Key1:=Range("Product"), Order1:=xlAscending,
key2:=Range("Qual"), order2:=xlAscending
With Range("Database")
Do While .Cells(i, 1) <> ""
If .Cells(i, 1) = Product Then
If Quality = "Any" Then
RowStart = i
Do While .Cells(i, 1).Value = Product
i=i+1
Loop
RowEnd = i - 1
Exit Do
ElseIf .Cells(i, 5) = Quality Then
RowStart = i
Do While .Cells(i, 1).Value = Product
If .Cells(i, 5).Value <> Quality Then
RowEnd = i - 1
Exit Do
End If
i=i+1
Loop
RowEnd = i - 1
Exit Do
End If
End If
i=i+1
Loop
End With
End If
103
DisplayProduct Procedure
Sub DisplayProduct()
Results.Value = "Search Results"
Results.Font.Bold = True
Range("Titles").Copy
Results.Offset(1, 0).PasteSpecial
With Range("Database")
If RowStart = 0 Then
Results.Offset(2, 0).Value = "No product in the database matches your
criteria."
Else
Range(.Cells(RowStart, 1), .Cells(RowEnd, 6)).Copy
Results.Offset(2, 0).PasteSpecial
End If
End With
Range(Results.Offset(2, 0), Results.Offset(2, 5)).Interior.ColorIndex = 0
End Sub
104
Main Sub Procedure
Sub Main()
Set Results = Worksheets("Products Search").Range("B25")
Range(Results, Results.Offset(20, 5)).Clear
Range(Results, Results.Offset(20, 5)).Interior.ColorIndex = 40
frmSearchPriority.Show
End Sub
105
Application Conclusion

The application is now complete.

Assign the procedures to the respective buttons and check the
functionality of the program.
106
Summary

There are several user form controls used to create a user interface:
–
–
–
–
–
–
–






Labels and Textboxes
Combo Boxes and List Boxes
Check Boxes, Option Buttons and Toggle Buttons
Command Buttons
Tab Strips and Multipage
Scroll Bar and Spin Buttons
Images and RefEdit
There are several properties associated with each of these controls.
There are also several event procedures associated with each of these controls.
Error checking is used in user interface to ensure that the user has entered
values compatible with the controls and variables in the program.
You can import and export forms.
Aside from form controls on the spreadsheet, buttons from drawing objects can
also be used as a navigating interface.
You can create a professional appearance for your application by using sheet
options and protecting the worksheet.
107
Additional Links

(place links here)
108