SpreadsheetBased Decision Support Systems Chapter 18 User Interface

Spreadsheet-Based Decision Support Systems Chapter 18: User Interface Prof. Name Position University Name name@email. com (123) 456 -7890

Overview v v v 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 v Creating a user form with various controls v Setting the properties of these controls and work with common events v Coding in form modules using event procedures, variable scope, and error checking v Creating a professional appearance for the workbook interface v Two user interface applications: one with controls on the spreadsheet and one with several user forms 3

User Form Controls v Labels and Textboxes v Combo Boxes and List Boxes v Check Boxes, Option Buttons and Toggle Buttons v Command Buttons v Tab Strips and Multipage v Scroll Bar and Spin Buttons v Images and Ref. Edit 4

User Forms v 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 v You will then see a blank form and the Control Toolbox 5

Figure 18. 2 v Each of the possible user form controls is labeled on this form. 6

Form Properties and Code v Create some code associated with the user form which can capture the actions taken by the user. v Use the Properties of these form items to name each item and understand the values that can be assigned to them. v Use public variables to transfer actions performed on the user form to the main code of the program. 7

Form Properties and Code (cont) v The most important property which we will use for all user form items is the Name property. v The name of an item from a user form is treated as an object in the code. v We recommend that the beginning of your user form item name should identify which time of item it is. v User forms will also have name property values. v 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 “frm. Menu”. 8

Form Properties and Code (cont) v 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. v 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. – frm. Menu. lbl. Quantity 9

Labels and Text Boxes v Labels and text boxes will be the most commonly used form items. v A label is used to – Give a description to any item on the form – Give general form description 10

Labels v The name of a label should begin with “lbl” followed by some descriptive name. v 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 v For example, to hide the “lbl. Quantity” label, you would type: – lbl. Quantity. Visible = False 11

Text Boxes v 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) v The name given to a text box in the Properties window should begin with “txt”. v We usually want to assign the value of a text box object to some variable in our code. – Do this using the Value property. v For example, if there was a quantity variable in our code to which we wanted to assign the “txt. Quantity” text box value, we would type: – quantity = txt. Quantity. Value 13

Combo Boxes and List Boxes v Combo boxes and list boxes are used to give the user a list of options from which they can select one item. v The main difference between combo boxes and list boxes is the way in which the data is displayed. v A combo box will list the first entry next to a drop-down button. v When the button is pressed, all other items in the list are shown. v 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 v The name given to a combo box should begin with “cbo”. v There are several important properties associated with combo boxes. v The main property we use to capture the user’s selection is the Value property. v For example, if we have the variable useritem which is associated with the user’s selection from the combo box of items called “cbo. Items”, we could use the following code to assign a value to our variable: – useritem = cbo. Items. Value 15

Combo Boxes (cont) v To specify the values to be listed in the combo box, we use the Row. Source property. v This property can have a value equal to a specified range in a worksheet or a range name. v For example, if the range A 5: B 10 has five rows and two columns of data which we want to show in the list box, we would set the Row. Source property as follows: – Worksheets(“Sheet 1”). Range(“A 5: B 10”). Name = “Options” – cbo. Itmes. Row. Source = “Options” 16

Combo Boxes (cont) v If the Row. Source of a combo box has more than one column, several other properties can be used. v The first is the Bound. Column property. v This property determines which column of the multicolumn data will contain the value which can be captured with the Value property. v For the above example, if we set the Bound. Column to 1, then regardless of what row of data is selected, only the data from column A will be stored in the Value property. – cbo. Items. Bound. Column = 1 17

Combo Boxes (cont) v Another useful property for multicolumn data is Column. Count. v Column. Count is used to set how many columns of the Row. Source 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. v If you want to show non-adjacent columns which may not be at the beginning of our data, use the Column. Widths property. v 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. – cbo. Items. Column. Count = 2 – cbo. Items. Column. Widths = “ 0”, “ 1” 18

Combo Boxes (cont) v Another useful property is the Column. Heads property. v This property can be set to True if there are column headings in the data which you want to display in the combo box. v There are some formatting properties specific to combo boxes such as List. Rows and Style. v List. Rows is used to specify the number of rows that should appear in the combo box. v 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 v A list box has basically the same functionality and several similar properties as a combo box. v 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. v The user cannot, therefore, enter a value into the list box. 20

List Boxes (cont) v The name given to a list box should begin with “lst”. v List boxes also use the Row. Source, Bound. Column, Column. Count, Column. Widths, Column. Heads, and List. Rows properties as described with combo boxes. 21

Check Boxes, Option Buttons, and Frames v Check boxes and option buttons are used to create Boolean selection options for the user. v Frames can be used to group these items or other related items. v 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 v The name given to a check box should begin with “chk”. v The Value property of check boxes can be used as it is with Boolean variables. If chk. Author. Value = True Then actions… End If -------------------If chk. Author Then actions… End If v We also use the Caption property to give a brief description to each check box. 23

Option Buttons v Option buttons imply a positive selection when selected. – That is a yes, true, on, etc value. – The opposite is true if they are unselected. v The name given to an option button should begin with “opt”. 24

Option Buttons (cont) v The Value property of option buttons can be used as it is with Boolean variables. If opt. Buy. Value = True Then actions… End If -------------------If opt. Buy Then actions… End If v We also use the Caption property to give a brief description to each option button. v We can also use option buttons with a frame. 25

Frames v 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. v The name of a frame should begin with “fra”. 26

Frames (cont) v Frames primarily use the Caption property. v 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. v This feature is only true for option buttons (not for check boxes, or toggle buttons). 27

v Toggle Buttons and Command Even though toggle buttons and Buttons command buttons are similar in appearance, they have very different features. v 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. v A toggle button name should begin with “tgl”. 28

Toggle Buttons v Toggle buttons also have Values similar to Boolean variables. If tgl. Year 1. Value = True Then actions… End If ------------------If tgl. Year 1 Then actions……. End If v Toggle buttons also use the Caption property to give a brief description of what the toggle button will select. 29

Command Buttons v Command buttons, unlike the controls we have learned so far, will be used for their associated event procedures more than for their properties. v The only property we will use often with this control (aside from Visible possibly) is the Caption property. v Command buttons should be named starting with “cmd”. The two command buttons we will use most often will be called “cmd. OK” and “cmd. Cancel”. v 30

Command Buttons (cont’d) v The main event procedure associated with command buttons is the Click event. – For the “cmd. OK” 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. v 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. v To close the current form at the end of this event procedure use the Unload Me statement. – The event procedure associated with the “cmd. Cancel” button will usually just have this line of code. 31

Command Button Code Sub cmd. OK_Click() quantity = txt. Quantity. Value With fra. Buy. Sell If. opt. Buy Then actions… Else. If. opt. Sell Then actions…. End If End With Unload Me frm. Menu. Show End Sub -----------------------Sub cmd. Cancel_Click() Unload Me End Sub 32

Tab Strips and Multi Page v Tab strips and multi page items allow you to organize user input options. v 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 v A tab strip should be named starting with “tab”. v Since a tab strip is a collection of tab objects, different properties of tabs can be considered sub properties of tab strip. – tab. Customers. Tab(customer 1) v The main property used with tab strips to capture a selected value is Selected. Item. v We can find the number of tabs in the selected tab strip by using the Count method. v 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 v The tabs have the same textbox control, but different values are shown for different tabs. 35

Multi Page v 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. v A multi page should be named starting with “mpg”. v The Value property is used with multi pages to denote the index of the particular page. 36

Figure 18. 16 v Each page of a multipage has a unique set of controls. 37

Scroll Bar and Spin Buttons v Scroll bars and spin buttons are both used to update values of other controls using event procedures. v Scroll bars can change text, numerical values, or other values of associated controls. v A scroll bar should be named starting with “scr”. 38

Scroll Bars v 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. v The value of the scroll bar, which is numerical, can be captured using the Value property. v There also the Min and Max properties which can be set for the scroll bar values, along with a Small. Change property to specify how much the scroll bar value should change for each change in the scroll bar position. v 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 scr. Size_Change() scr. Size. Min = 1 scr. Size. Max = 30 scr. Size. Small. Change = 1 Select Case scr. Size. Value Case 1 to 5 txt. Size. Value = “too small” Case 6 to 15 txt. Size. Value = “small range, but good” Case 16 to 25 txt. Size. Value = “large range, but good” Case 26 to 30 txt. Size. Value = “too large” End Select End Sub 40

Spin Buttons v Spin buttons are similar to scroll bars, except that they only increment or decrement numerical values of other controls v A spin button name should start with “spn”. 41

Spin Buttons (cont) v Spin buttons also use the Change event procedure primarily to associate its values with the values of another control. v The Value property, and Min, Max, and Small. Change properties are also used. v The Orientation property can also be applied to spin buttons. 42

Spin Button Code Sub spn. Height_Change() spn. Height. Min = 4. 5 spn. Height. Max = 7. 0 spn. Height. Small. Change = 0. 25 txt. Height. Value = spn. Height. Value End Sub 43

Images and Ref. Edit v Images allow you to display a picture in the form. v An image can be named starting with “img” and the picture name. 44

Images v To assign an image to the image window, use the Picture property and Browse options. v You can use the Picture. Alignment and Picture. Size. Mode properties to adjust the size, crop, or zoom of your image. 45

Ref. Edit v v Ref. Edit 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. v The name for Ref. Edit should begin with “ref”. v 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 v There are some properties associated with the user form itself which can be useful. v 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) v Another property of the user form is Show. Modal. – 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) v There are some general properties that apply to the user form as well as to most controls. v These include general formatting properties such as – Font, – Back. Color – Fore. Color v As we have mentioned already some other common properties found for most if not all controls are Name, Caption, and Visible. v There are three other important common properties used with most/all controls, they are – Tab. Index – Tab. Stop – Locked 49

User Form Options (cont) v Tab. Index and Tab. Stop control the tabbing order in which the user can move through items on the user form with the Tab button of their keyboard. – The Tab. Index 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 Tab. Stop 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) v Similar to Tab. Stop, the Locked property takes True or False values and allows you to prevent the user from entering or changing any values of a control. v 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. v 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 v Event procedures are code procedures which are associated with an event or action that occurs on a control. v 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. v We will review a few of the more commonly used event procedures. 52

Figures 18. 21 and 18. 22 v Example of selecting an event procedure for an option button. 53

Event Procedures (cont’d) v A commonly used event procedure is the Initialize procedure of the user form. – This procedure will run when the form is first opened. v The main event procedure associated with command buttons is the Click event. v Use the Show method of the form object to open the named form. v Use the Unload Me statement to close the current form. 54

Event Procedures (cont’d) Sub cmd. OK_Click() quantity = txt. Quantity. Value With fra. Buy. Sell If. opt. Buy. Then actions 1… Else. If. opt. Sell Then actions 2…. End If End With Unload Me frm. Menu. Show End Sub -----------------------Sub cmd. Cancel_Click() Unload Me End Sub 55

Event Procedures (cont’d) v 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 scr. Size_Change() scr. Size. Min = 1 scr. Size. Max = 30 scr. Size. Small. Change = 1 Select Case scr. Size. Value Case 1 to 5 txt. Size. Value = “too small” Case 6 to 15 txt. Size. Value = “small range, but good” Case 16 to 25 txt. Size. Value = “large range, but good” Case 26 to 30 txt. Size. Value = “too large” End Select End Sub 56

Event Procedures (cont’d) 57

Variable Scope v Any variable declared within a form module can only be used directly in the procedures for that particular form. v 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) v Consider the following code within the frm. Main module. Public User. Input As Integer Sub cmd. OK_Click() User. Input = txt. Input. Value Unload Me End Sub v Even though the integer variable User. Input has been declared as a public variable in the form module, it can not be referred to directly in any other module. v That is, if we wanted to use the input value given by the user in the txt. Input textbox on the form in another module in the application, we could not use the User. Input variable as is. 59

Variable Scope (cont) v 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. v That is, to refer to the User. Input variable outside of the frm. Main code, we must type – frm. Main. User. Input v In the same way, we can use the form name to refer to any of its controls outside of the form modul – frm. Main. lbl. Quantity. Visible = True 60

Variable Scope (cont) v However, it is important to keep in mind that all form control values are reset when the Unload Me statement is used. v 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 cmd. OK_Click() ‘txt. Input. Value has been entered by user Call Calculations Unload Me End Sub 61

Variable Scope (cont) v 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. v 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 cmd. OK_Click() User. Input = txt. Input. Value Unload Me Call Calculations End Sub 62

Error Checking v Any time we are receiving something from the user, we have to check for errors. v 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 Type. Name(ctl) = “Text. Box” Then If ctl. Value = “” or Is. Numeric(ctl) = False Then Msg. Box “Please enter a numeric value in all textboxes. ” ctl. Set. Focus End Sub End If Next 63

Error Checking (cont) v The Control data type is another example of an Object data type. v The For Each, Next loop checks all of the controls in our form. v Me. Controls refers to the set of controls in the current form. v To determine the type of control, we use the Type. Name function with an If, Then statement. v The statement ctl. Set. Focus 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 v Any form you create can be exported, or saved as a template. v 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. v 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 v A standard form template named frm. Basic may be imported several times within the same application. 66

Navigating v 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”. v They should be placed at the top of the worksheet in a visible spot. v The code for these macros can be identical or call an identical function. 67

Navigating (cont) v A useful property of these buttons, or shape objects, is the Visible property. v By naming these buttons appropriately, you can use the Shape object and Visible property to hide and show different buttons. v This is a good way to prevent your user from being confused or overwhelmed by too many options. 68

Professional Appearance v Protecting the Worksheet v Sheet Options 69

Protecting the Worksheet v To prevent your user from changing formatting or title values in any worksheet tables or other features, you can protect the worksheet. v 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) v After the appropriate cells have been locked and unlocked, select Tools > Protection > Protect Worksheet from the menu. v 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 v There are some other options which can be set for each worksheet in your program to finalize the version which the user will see. v To view these options, select Tools > Options from the menu. v Some of the options we usually change include – Not viewing gridlines – Hiding sheet tabs – Hiding column and row headings 72

Applications v Real Estate Search Spreadsheet Application v Product Search Form 73

Real Estate Description v Consider a real estate search application in which a user can search for houses based on certain criteria. v This search is performed on an Excel database of real estate data. v 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 v We use a scroll bar to determine the max price criteria. v We name the control: scr. Price. v We set the Max value at 200, 000. v We set the Min value at 75, 000. v We set the Small. Change and Large. Change values both to 1, 000. 77

Max Price Criteria (cont) v The Linked. Cell property holds the value of a cell name in which the value of the control will be displayed. v In this application, we set the Linked. Cell property of the scroll bar to B 6. v 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 v We then have a textbox for the user to specify the minimum area criteria. v We name the control, txt. Area, and do not set any other property values. 79

Min Number of Bedrooms and Bathrooms Criteria v The next two controls are spin buttons. v We name them spn. Bed and spn. Bath respectively. v The most important properties for spin buttons are Max, Min, and Small. Change. – For spn. Bed, we set these equal to 5, 1, and 1 respectively. – For spn. Bath we set these equal to 3, 1, and 1 respectively. v We will also use the Linked. Cell property for both of these controls in order to display the values of the spin buttons. – For spn. Bed, we set the Linked. Cell property to B 10. – For spn. Bath, we set the Linked. Cell property to B 12. 80

Location Criteria v We use a combo box to specify the location criteria of the house. v We name this combo box as cmb. Location. v When using a combo box or list box on the spreadsheet, the Row. Source property is called the List. Fill. Range. – In cells I 5: I 8, 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 v We first create a “Search” button, named cmd. Search, which we will use to capture the current values of all the controls as search criteria. v We then create a “View All” button, named cmd. Reset, which we will use to reset any filtering done and show the original database. 83

Filtering v We will use the Click event procedure of the cmd. Search button to filter the house information based on the criteria specified. v In order to filter the house data, we will be using the Auto. Filter method. v 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) v 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”). Auto. Filter Field: =(number), Criteria: = “(inequality)” (value), Operator: =xl. And v 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) v For the price criteria, the inequality will be “<=”. v For the area criteria, the inequality will be “>=”. v For the number of bedrooms and number of bathrooms, the inequality will also be “>=”. v For the location criteria, we can ignore the inequality and just give the value (we can also ignore the Operator argument). v The values for each criteria will be the value of the control which is involved in the criteria. 86

Figure 18. 28 v The “Search” button procedures 87

“View All” Button Event Procedure v For this button we want to remove all filtering from the data. v To remove any filtering, we just simply type the Auto. Filter method without any fields or criteria: Range(“Houses”). Auto. Filter 88

Show All Procedure v We notice that whatever filtering is done first, remains as multiple criteria are specified. v Therefore, before each criteria’s filtering is done, we must first reset our data to be unfiltered. v We create a small procedure to call multiple times. 89

Worksheet Activate Event Procedure v This procedure could be used to call the Show. All function to ensure that the entire house data is displayed when the user first opens the application. v We can also ensure that the row source of the combo box is initialized; again, we use the List. Fill. Range property of the spreadsheet combo box to do this. 90

Application Conclusion v The application is now complete. v Check the functionality of the program. 91

Product Search Description v In this application, we are designing a search form for a product in a computer product database. v The program finds a product based on certain search priorities and search criteria. 92

Preparation v There are only two sheets needed for this application – “Products Search” – “Products Database” v The “Products Search” sheet is the welcome sheet. – A brief description of he application is given, and there is a Start button. v 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. v After setting up these sheets some ranges will be named as further preparation. 93

Figure 18. 32 94

Figure 18. 33 95

User Forms v There will be two main forms used in this application – “Search Priority” – “Search Criteria” v 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) v 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. v 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 User. Form_Initialize() cmb. Products. Row. Source = "Prod. List" chk. Standard = True chk. Premium = True txt. Price. Limit. Value = 100 End Sub -----------------------------Private Sub spn. Price. Limit_Change() txt. Price. Limit = spn. Price. Limit. Value End Sub ------------------------------ 101

“Search Criteria” Code (cont) Private Sub cmd. Find. Product_Click() Product = cmb. Products. Value If Quality. Level Then If chk. Standard And chk. Premium Then Quality = "Any" Else. If chk. Standard Then Quality = "Standard" Else. If chk. Premium Then Quality = "Premium" End If If Is. Null(txt. Price. Limit. Value) = False And txt. Price. Limit. Value > 0 Then Best. Price = txt. Price. Limit. Value Else Best. Price = 0 End If Unload Me Call Find. Product End Sub 102

Find. Product Procedure i=1 Row. Start = 0 Row. End = 0 If Price Then 'sort by product and then by price Range("Database"). Sort Key 1: =Range("Product"), Order 1: =xl. Ascending, key 2: =Range("Price"), order 2: =xl. Ascending With Range("Database") Do While. Cells(i, 1) <> "" If. Cells(i, 1) = Product Then Row. Start = i Do While. Cells(i, 1). Value = Product If. Cells(i, 6). Value > Best. Price Then Row. End = i - 1 Exit Do End If i=i+1 Loop End With 103

Find. Product Procedure (cont) Else. If Quality. Level Then 'sort by product and then by quality Range("Database"). Sort Key 1: =Range("Product"), Order 1: =xl. Ascending, key 2: =Range("Qual"), order 2: =xl. Ascending With Range("Database") Do While. Cells(i, 1) <> "" If. Cells(i, 1) = Product Then If Quality = "Any" Then Row. Start = i Do While. Cells(i, 1). Value = Product i=i+1 Loop Row. End = i - 1 Exit Do Else. If. Cells(i, 5) = Quality Then Row. Start = i Do While. Cells(i, 1). Value = Product If. Cells(i, 5). Value <> Quality Then Row. End = i - 1 Exit Do End If i=i+1 Loop End With End If 104

Display. Product Procedure Sub Display. Product() Results. Value = "Search Results" Results. Font. Bold = True Range("Titles"). Copy Results. Offset(1, 0). Paste. Special With Range("Database") If Row. Start = 0 Then Results. Offset(2, 0). Value = "No product in the database matches your criteria. " Else Range(. Cells(Row. Start, 1), . Cells(Row. End, 6)). Copy Results. Offset(2, 0). Paste. Special End If End With Range(Results. Offset(2, 0), Results. Offset(2, 5)). Interior. Color. Index = 0 End Sub 105

Main Sub Procedure Sub Main() Set Results = Worksheets("Products Search"). Range("B 25") Range(Results, Results. Offset(20, 5)). Clear Range(Results, Results. Offset(20, 5)). Interior. Color. Index = 40 frm. Search. Priority. Show End Sub 106

Application Conclusion v The application is now complete. v Assign the procedures to the respective buttons and check the functionality of the program. 107

Summary v There are several user form controls used to create a user interface: – – – – v v v 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 Ref. Edit There are several properties associated with each of these controls. There 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 v (place links here) 109