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

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
5
Figure 18.2

Each of the possible user form controls is labeled on this form.
6
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 items to name each item 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.
7
Form Properties and Code (cont)

The most important property which we will use for all user form items is the
Name property.

The name of an item from a user form is treated as an object in the code.

We recommend that the beginning of your user form item name should identify
which time of item it is.

User forms will also have name property values.

For example, the name of a form should begin with “frm” followed by some
descriptive name.
– A form which gives the user some basic menu options might be called “frmMenu”.
8
Form Properties and Code (cont)

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
9
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 item on the form
– Give general form description
10
Labels

The name of a label should begin with “lbl” followed by some descriptive
name.

The only manipulation of labels that we use in case studies is hiding and
un-hiding.
– To set these properties, you would use the Visible property

For example, to hide the “lblQuantity” label, you would type:
– lblQuantity.Visible = False
11
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.
12
Text Boxes (cont)

The name given to a text box in the Properties window should begin with
“txt”.

We usually want to assign the value of a text box object to some variable
in our code.
– Do this 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
13
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.
14
Combo Boxes

The name given to a combo box should begin with “cbo”.

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 useritem which is associated with
the user’s selection from the combo box of items called “cboItems”, we
could use the following code to assign a value to our variable:
– useritem = cboItems.Value
15
Combo Boxes (cont)

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”
– cboItmes.RowSource = “Options”
16
Combo Boxes (cont)

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.
– cboItems.BoundColumn = 1
17
Combo Boxes (cont)

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.
– Any other number can be used 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.
– cboItems.ColumnCount = 2
– cboItems.ColumnWidths = “0”, “1”
18
Combo Boxes (cont)

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:
– One allows the user to enter data if a selection from the combo box is not made.
– The other does not.
19
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.
– That is, there is no drop-down button as with combo boxes.

The user cannot, therefore, enter a value into the list box.
20
List Boxes (cont)

The name given to a list box should begin with “lst”.

List boxes also use the RowSource, BoundColumn, ColumnCount,
ColumnWidths, ColumnHeads, and ListRows properties as described
with combo boxes.
21
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.
22
Check Boxes

The name given to a check box should begin with “chk”.

The Value property of check boxes can be used as it is 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.
23
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 unselected.

The name given to an option button should begin with “opt”.
24
Option Buttons (cont)

The Value property of option buttons can be used as it is 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.
25
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”.
26
Frames (cont)

Frames primarily use the Caption property.

Frames become more interesting when applied to option buttons as this
makes the option buttons mutually exclusive.
– That is, when option buttons are used without a frame, more than one can be
selected (as with check boxes).
– However, 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).
27

Toggle Buttons and Command
Even though toggle buttons and Buttons
command buttons are similar in
appearance, they have very different features.

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”.
28
Toggle Buttons

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.
29
Command Buttons

Command buttons, unlike the controls we have learned so far, will be
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”.

30
Command Buttons (cont’d)

The main event procedure associated with command buttons is the
Click event.
– For the “cmdOK” button, for example, the even procedure associated with
this button would be where we may assign variables to text box values or
create an If, Then statement with some option buttons.

To show another form after the user has finished filling the current form
use the Show method.
– We simply state the name of the user form we want to show next followed
by the Show method.

To close the current form at the end of this event procedure use the
Unload Me statement.
– The event procedure associated with the “cmdCancel” button will usually
just have this line of code.
31
Command Button Code
Sub cmdOK_Click()
quantity = txtQuantity.Value
With fraBuySell
If .optBuy Then
actions…
ElseIf .optSell Then
actions….
End If
End With
Unload Me
frmMenu.Show
End Sub
---------------------------------------------Sub cmdCancel_Click()
Unload Me
End Sub
32
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.
– That is 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.
33
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
SelectedItem.

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.
34
Figure 18.13

The tabs have the same textbox control, but different values are shown
for different tabs.
35
Multi Page

A multi page control, on the other hand, 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.
36
Figure 18.16

Each page of a multipage has a unique set of controls.
37
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”.
38
Scroll Bars

The Change, or Scroll, event procedures are most commonly used to
associate the change in the scroll bar values with an effect on another
control value.

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 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.
39
Scroll Bar Code
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”
40
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”.
41
Spin Buttons (cont)

Spin buttons also use the Change event procedure primarily to associate
its values with the values of another control.

The Value property, and Min, Max, and SmallChange properties are
also used.

The Orientation property can also be applied to spin buttons.
42
Spin Button Code
Sub spnHeight_Change()
spnHeight.Min = 4.5
spnHeightMax = 7.0
spnHeight.SmallChange = 0.25
txtHeight.Value = spnHeight.Value
End Sub
43
Images and RefEdit

Images allow you to display a picture in the form.

An image can be named starting with “img” and the picture name.
44
Images

To assign an image to the image window, use the Picture property and
Browse options.

You can use the PictureAlignment and PictureSizeMode properties to
adjust the size, crop, or zoom of your image.
45
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.
46
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.
47
User Form Options (cont)

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.
– However, 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.
48
User Form Options (cont)

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
49
User Form Options (cont)

TabIndex and TabStop control the tabbing order in which the user can
move through items 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.
50
User Form Options (cont)

Similar to TabStop, the Locked property takes True or False values and
allows you to prevent the user from entering or changing any values of a
control.

This may be useful if you want to, for example, have sequential forms
and show some of the data entered on the first form in a form, or forms,
which follow.

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.
51
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.
52
Figures 18.21 and 18.22

Example of selecting an event procedure for an option button.
53
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.
54
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
55
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”
56
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 and
Check Boxes
Click, Change
57
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.
58
Variable Scope (cont)

Consider the following code within the frmMain module.
Public UserInput As Integer
Sub cmdOK_Click()
UserInput = txtInput.Value
Unload Me
End Sub

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.

That is, 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.
59
Variable Scope (cont)

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.

That is, to refer to the UserInput variable outside of the frmMain code, we
must type
– frmMain.UserInput

In the same way, we can use the form name to refer to any of its controls
outside of the form modul
– frmMain.lblQuantity.Visible = True
60
Variable Scope (cont)

However, it is important to keep in mind that all form control values are
reset when the Unload Me statement is used.

Therefore, 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
61
Variable Scope (cont)

However, the Unload Me statement is also used to close the form.
– That is, you do not want to bring the user through the rest of the application
with the initial input form still displayed.

We therefore, generally recommend assigning form control values to
variables defined as public variables outside of the form module.
– This allows you to make the Unload Me statement without losing the variable
values.
Sub cmdOK_Click()
UserInput = txtInput.Value
Unload Me
Call Calculations
End Sub
62
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
63
Error Checking (cont)

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.
64
Importing and Exporting Forms

Any form you create can be exported, or saved as a template.

To save a form as a template, simply 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.
65
Figure 18.23

A standard form template named frmBasic may be imported several
times within the same application.
66
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.
67
Navigating (cont)

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.
68
Professional Appearance

Protecting the Worksheet

Sheet Options
69
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.
70
Protecting the Worksheet (cont)

After the appropriate cells have been locked and unlocked, select Tools
> Protection > Protect Worksheet from the menu.

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.
71
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 Tools > Options from the menu.

Some of the options we usually change include
– Not viewing gridlines
– Hiding sheet tabs
– Hiding column and row headings
72
Applications

Real Estate Search Spreadsheet Application

Product Search Form
73
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.
74
Figure 18.24
75
Figure 18.25
76
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.
77
Max Price Criteria (cont)

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.
78
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.
79
Min Number of Bedrooms and Bathrooms Criteria

The next 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.
80
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”.
81
Figure 18.27
82
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.
83
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.
84
Filtering (cont)

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
85
Filtering (cont)

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.
86
Figure 18.28

The “Search” button procedures
87
“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
88
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.
89
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.
90
Application Conclusion

The application is now complete.

Check the functionality of the program.
91
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.
92
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.
93
Figure 18.32
94
Figure 18.33
95
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, most available, or quality level.
– This form will use a frame with three option buttons to give the user these
options.
– A description label is given.
– The typical two command buttons are used.
 OK and Cancel
96
Figure 18.34
97
Figure 18.35
98
User Forms (cont)

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 next to relative labels.
– We name the OK button on this form Find Product.

Some of these labels and frames may not appear depending on the
users choice in the search priority form.
99
Figures 18.36 and 18.37
100
“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
-----------------------------------------------------------
101
“Search Criteria” Code (cont)
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
102
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
103
FindProduct Procedure (cont)
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
104
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
105
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
106
Application Conclusion

The application is now complete.

Assign the procedures to the respective buttons and check the
functionality of the program.
107
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.
108
Additional Links

(place links here)
109