EIN 4905/ESI 6912 Decision Support Systems Excel

1
Spreadsheet-Based Decision Support Systems
Chapter 18 User Interface
Prof. Name
name_at_email.com Position
[phone] University Name
2
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

3
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

4
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

5
User Forms

• One of the best tools VBA for Excel offers to
  help in this communication with the user is user
  forms.
• Insert gt 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

6
Figure 18.2

• 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 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.

8
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.

9
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

10
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

11
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

12
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.

13
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

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
  cbo.
• There are several important properties associated
  with combo boxes.
• The main property we use to capture the users
  selection is the Value property.
• For example, if we have the variable useritem
  which is associated with the users 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

16
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 A5B10 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(A5B10).Name
  Options
• cboItmes.RowSource Options

17
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

18
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

19
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.

20
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.

21
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.

22
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.

23
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.

24
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.

25
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.

26
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.

27
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).

28
Toggle Buttons and Command Buttons

• Even though toggle buttons and 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.

29
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.

30
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.

31
Command Buttons (contd)

• 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.

32
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

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.
• 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.

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 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.

35
Figure 18.13

• The tabs have the same textbox control, but
  different values are shown for different tabs.

36
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.

37
Figure 18.16

• Each page of a multipage has a unique set of
  controls.

38
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.

39
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.

40
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 too small
• Case 6 to 15
• txtSize.Value small range, but good
• Case 16 to 25
• txtSize.Value large range, but good
• Case 26 to 30
• txtSize.Value too large
• End Select
• End Sub

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.

42
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.

43
Spin Button Code

• Sub spnHeight_Change()
• spnHeight.Min 4.5
• spnHeightMax 7.0
• spnHeight.SmallChange 0.25
• txtHeight.Value spnHeight.Value
• End Sub

44
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.

45
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.

46
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.

47
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 Excels point system.

48
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.

49
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

50
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.

51
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.

52
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.

53
Figures 18.21 and 18.22

• Example of selecting an event procedure for an
  option button.

54
Event Procedures (contd)

• 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.

55
Event Procedures (contd)

• 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

56
Event Procedures (contd)

• 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 too small
• Case 6 to 15
• txtSize.Value small range, but good
• Case 16 to 25
• txtSize.Value large range, but good
• Case 26 to 30
• txtSize.Value too large
• End Select
• End Sub

57
Event Procedures (contd)
58
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.

59
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.

60
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

61
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

62
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

63
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

64
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 users cursor
  is in the first place where an error was found.

65
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 gt Import File at any time.

66
Figure 18.23

• A standard form template named frmBasic may be
  imported several times within the same
  application.

67
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.

68
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.

69
Professional Appearance

• Protecting the Worksheet
• Sheet Options

70
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.

71
Protecting the Worksheet (cont)

• After the appropriate cells have been locked and
  unlocked, select Tools gt Protection gt 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.

72
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 gt Options
  from the menu.
• Some of the options we usually change include
• Not viewing gridlines
• Hiding sheet tabs
• Hiding column and row headings

73
Applications

• Real Estate Search Spreadsheet Application
• Product Search Form

74
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.

75
Figure 18.24
76
Figure 18.25
77
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.

78
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.

79
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.

80
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.

81
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 I5I8, 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.

82
Figure 18.27
83
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.

84
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.

85
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),
  OperatorxlAnd
• 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

86
Filtering (cont)

• For the price criteria, the inequality will be
  lt.
• For the area criteria, the inequality will be
  gt.
• For the number of bedrooms and number of
  bathrooms, the inequality will also be gt.
• 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.

87
Figure 18.28

• The Search button procedures

88
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

89
Show All Procedure

• We notice that whatever filtering is done first,
  remains as multiple criteria are specified.
• Therefore, before each criterias filtering is
  done, we must first reset our data to be
  unfiltered.
• We create a small procedure to call multiple
  times.

90
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.

91
Application Conclusion

• The application is now complete.
• Check the functionality of the program.

92
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.

93
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.

94
Figure 18.32
95
Figure 18.33
96
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

97
Figure 18.34
98
Figure 18.35
99
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.

100
Figures 18.36 and 18.37
101
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
• --------------------------------------------------
  ---------

102
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 gt 0 Then
• BestPrice txtPriceLimit.Value
• Else
• BestPrice 0
• End If

103
FindProduct Procedure

• i 1
• RowStart 0
• RowEnd 0
• If Price Then
• 'sort by product and then by price
• Range("Database").Sort Key1Range("Produc
  t"), Order1xlAscending,
• key2Range("Price"), order2xlAscending
• With Range("Database")
• Do While .Cells(i, 1) ltgt ""
• If .Cells(i, 1) Product Then
• RowStart i
• Do While .Cells(i, 1).Value
  Product
• If .Cells(i, 6).Value gt
  BestPrice Then
• RowEnd i - 1
• Exit Do
• End If
• i i 1
• Loop

104
FindProduct Procedure (cont)

• ElseIf QualityLevel Then
• 'sort by product and then by quality
• Range("Database").Sort Key1Range("Produc
  t"), Order1xlAscending,
• key2Range("Qual"), order2xlAscending
• With Range("Database")
• Do While .Cells(i, 1) ltgt ""
• 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
  ltgt Quality Then
• RowEnd i - 1

105
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

106
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

107
Application Conclusion

• The application is now complete.
• Assign the procedures to the respective buttons
  and check the functionality of the program.

108
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.

109
Additional Links

• (place links here)