1 / 108

User Interface Applications in Excel: Creating Professional Forms

Explore creating user forms with various controls, coding event procedures, error checking in Excel. Learn to enhance the workbook interface and create professional appearances.

mschott
Download Presentation

User Interface Applications in Excel: Creating Professional Forms

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Spreadsheet-Based Decision Support Systems Chapter 18: User Interface Prof. Name name@email.com Position (123) 456-7890 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 in Excel • Excel offers a simple tool to cerate a User Form without using VBA. • Use Excel Options dialog box to add the Form command in Quick Access toolbar. • Select the range of data in Excel. • Click on the Form command to create the form displayed in the figure.

  6. User Forms in VBA • One of the best tools VBA for Excel offers to help in this communication with the user is user forms. • Insert> User Form from the menu in the VBE • Insert icon as seen when inserting modules • You will then see a blank form and the Control Toolbox. • The Control Toolbox provides available controls which can be placed on the form.

  7. Figure 18.3 • Each of the possible user form controls is labeled on this form.

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

  9. Form Properties and Code (cont’d) • The most important property which we will use for all user form itemsis the Name property. • The name of a control from a user form is treated as an object in the code. • We recommend that the beginning of your user form control name identify which type of control it is. . • User forms will also have name property values. • For example, the name of a form may begin with “frm” followed by some descriptive name. • A form which gives the user some basic menu options might be called “frmMenu”.

  10. Form Properties and Code (cont’d) • If you are writing code associated with the user form which contains the object you want to manipulate, you only have to enter the name of the object directly and then use any property or method available. • To manipulate a user form object while in another user form code or in any other module, type the name of the form first followed by a period and the name of the object. • frmMenu.lblQuantity

  11. Figure 18.5 • To view the code of our current user form, use the ViewCode button. • To view the form design of our current form code, use ViewObject buttons.

  12. Labels and Text Boxes • Labels and textboxes will be the most commonly used form items. • A label is used to: • Give a description to any control on the form. • Give general form description.

  13. Labels • The name of a label should begin with “lbl” followed by some descriptive name. • The only manipulation of labels that we developing applications is with the Caption property and the Visible property. • Caption property is used to change the text displayed in the label. • Visible property takes True/False values. • For example, to hide the “lblQuantity” label, you would type: • lblQuantity.Visible = False

  14. Text Boxes • A text box is used to allow the user to enter some value. This value may be a string or number. • The label next to the text box should specify the kind of value the user should enter. • The name given to a text box in the Properties window should begin with “txt”. • Assign the value of a text box object to some variable in the code using the Value property • For example, if there was a quantity variable in our code to which we wanted to assign the “txtQuantity” text box value, we would type: • quantity = txtQuantity.Value

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

  16. Combo Boxes • The name given to a combo box should begin with “cmb” followed by a descriptive name. • There are several important properties associated with combo boxes. • The main property we use to capture the user’s selection is the Value property. • For example, if we have the variable usercontrol which is associated with the user’s selection from the combo box of controls called “cmbControls”, we could use the following code to assign a value to our variable: • usercontrol = cmbControls.Value

  17. Combo Boxes (cont’d) • To specify the values to be listed in the combo box, we use the RowSource property. • This property can have a value equal to a specified range in a worksheet or a range name. • For example, if the range A5:B10 has five rows and two columns of data which we want to show in the list box, we would set the RowSource property as follows: • Worksheets(“Sheet1”).Range(“A5:B10”).Name = “Options” • cmbItems.RowSource = “Options”

  18. Combo Boxes (cont’d) • Related to the RowSource property is the ControlSource property. • This property allows us to output directly to the spreadsheet the selection made by the users. • The value of the ControlSource property must be a range name, just as with the RowSource property. • For example, to output the selection of the combo box cmbControls to the cell A20, we would set the ControlSource property as follows: • cmbControls.ControlSource = “A20”

  19. Combo Boxes (cont’d) • If the RowSource of a combo box has more than one column, several other properties can be used. • The first is the BoundColumn property. • This property determines which column of the multicolumn data will contain the value which can be captured with the Value property. • For the above example, if we set the BoundColumn to 1, then regardless of what row of data is selected, only the data from column A will be stored in the Value property. • cmbControls.BoundColumn = 1

  20. Combo Boxes (cont’d) • Another useful property for multicolumn data is ColumnCount. • ColumnCount is used to set how many columns of the RowSource data should be displayed in the combo box. • If this value is 0, no columns are displayed. • If it is -1, all columns are displayed. • We can use any other number less than 10 to display the corresponding number of columns from the data. . • If you want to show non-adjacent columns which may not be at the beginning of our data, use the ColumnWidths property. • If we set the column width of column A to 0, and set column B to some non-zero width value, then only column B will be displayed. • cmbControls.ColumnCount = 2 • cmbControls.ColumnWidths = “0”, “1”

  21. Combo Boxes (cont’d) • Another useful property is the ColumnHeads property. • This property can be set to True if there are column headings in the data which you want to display in the combo box. • There are some formatting properties specific to combo boxes such as ListRows and Style. • ListRows is used to specify the number of rows that should appear in the combo box. • The Style property has two main options: • fmStyleDropDownCombo option allows the user to enter data if a selection from the combo box is not made. • fmStyleDropDownList option does not.

  22. Combo Boxes (cont’d) • Two other useful properties of the combo box are ListIndex and List. • The ListIndex property will return the index value of the control that was selected (the index for combo boxes begins at 1; the index for list boxes begins at 0). • The List property is used with the index of a control to select a particular control from the list. • For example, to initialize a combo box to show the first control in the list to be selected, we would type: • cmbControls.Value = cmbQuantity.List(1)

  23. List Boxes • A list box has basically the same functionality and several similar properties as a combo box. • A list box will list all items to be selected in a single box. • There is no drop-down button as with combo boxes. • The user cannot enter a value into the list box. • The name given to a list box should begin with “lst” followed by a descriptive name.

  24. List Boxes (cont’d) • List boxes also use the RowSource, BoundColumn, ColumnCount, ColumnWidths, ColumnHeads, ListRows, ListIndex, and List properties as described with combo boxes. • The list box has a unique property called MultiSelect. • The MultiSelect property determines if users can select more than one control from the list box. • fmMultiSelectSingle: Select only one control at a time. • fmMultiSelectMulti : Select more than one control by clicking on several from the list. • fmMultiSelectExtended : Select a section of adjacent or non-adjacent controls from the list box by pressing Shift or CTRL keys respectively.

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

  26. Check Boxes • The name given to a check box should begin with “chk”. • The Value property of check boxes can be used as with Boolean variables. If chkAuthor.Value = True Then actions… End If --------------------------------------- If chkAuthor Then actions… End If • We also use the Caption property to give a brief description to each check box.

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

  28. Option Buttons (cont’d) • The Value property of option buttons can be used as with Boolean variables. If optBuy.Value = TrueThen 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.

  29. Frames • A frame groups similar items together. • For example, it can be used to group text boxes which contain related data or check boxes which apply to the same category. • The name of a frame should begin with “fra” followed by a descriptive name.

  30. Frames (cont’d) • Frames primarily use the Caption property and Visible property. • Frames become more interesting when applied to option buttons as this makes the option buttonsmutually exclusive. • When option buttons are used without a frame, more than one can be selected (as with check boxes). • When option buttons are placed inside a frame, only one can be selected at a time. • This feature is only true for option buttons (not for check boxes, or toggle buttons).

  31. Toggle Buttons • Toggle buttons are similar to check boxes and option buttons in that they imply a positive selection when clicked. • That is a yes, true, on, etc value. • The opposite is true if they are un-clicked. • A toggle button name should begin with “tgl” followed by a descriptive name.

  32. Toggle Buttons (cont’d) • Toggle buttons also have Values similar to Boolean variables. If tglYear1.Value = TrueThen 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.

  33. Command Buttons • Command buttons, are used for their associated event procedures more than for their properties. • The only property we will use often with this control (aside from Visible possibly) is the Caption property. • Command buttons should be named starting with “cmd”. • The two command buttons we will use most often will be called “cmdOK” and “cmdCancel”.

  34. Tab Strips and Multi Page • Tab strips and multi page items allow you to organize user input options. • Tab strips group data into different sections of this one control. • All sections or tabs have the same layout. • One tab strip will have a set of controls which will appear on each tab. • Each tab can be associated with a group of data to which the tab strip information belongs.

  35. Tab Strips • A tab strip should be named starting with “tab”. • Since a tab strip is a collection of tab objects, different properties of tabs can be considered sub properties of tab strip. • tabCustomers.Tab(customer1) • The main property used with tab strips to capture a selected value is SelectedControl. • We can find the number of tabs in the selected tab strip by using the Count method. • To add a new tab to the tab strip, just right-click on the tab strip in the design view of the user form in the VBE and choose “new page” from the list of options.

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

  37. Figure 18.15 • To change the value of the textbox, we need to determine which tab the users have selected. • We use the Change event procedure of the tab strip and a SelectCase statement to check the value of the tab strip.

  38. Multi Page • A multi page control can be considered a collection of individual form objects, or pages. • Each page can have a different layout and is treated as a separate form. • A multi page should be named starting with “mpg”. • The Value property is used with multi pages to denote the index of the particular page.

  39. Figure 18.17 • Each page of a multipage has a unique set of controls.

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

  41. Scroll Bars • The value of the scroll bar, which is numerical, can be captured using the Value property. • There are also the Min and Max properties which can be set for the scroll bar values, along with a SmallChange and LargeChange property to specify how much the scroll bar value should change for each change in the scroll bar position. • There is also an Orientation property which can be used to set the scroll bar position to vertical or horizontal.

  42. Spin Buttons • Spin buttons are similar to scroll bars, except that they only increment or decrement numerical values of other controls • A spin button name should start with “spn”. • We also use the Value property and Min, Max, and SmallChange properties. • We can also apply the Orientation property to spin buttons.

  43. Images • Images allow you to display a picture in the form. • An image can be named starting with “img” and the picture name. • To assign an image to the image window, use the Picture property and Browse options. • We can use the PictureAlignment and PictureSizeMode properties to adjust the alignment and size of our image.

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

  45. User Form Options • There are some properties associated with the user form itself which can be useful. • There are several Position properties that can be modified. • To view these, use the Categorized tab of the Properties Window and scroll to the Position category. • The values of these positions are relative to the left and top margins of the worksheet and are measured with Excel’s point system. • To align several controls on the form, we can select and right-click and choose Align from the list of drop-down options.

  46. User Form Options (cont’d) • Another property of the user form is ShowModal. • When set to True, the form must be completed before the user can return to the program or use any worksheet. • When this property is set to False, the form becomes modeless, or a floating form. • When the user form is modeless, actions can occur on the worksheet and the program can continue while the form is still showing. • This can be useful for navigational purposes or to have a constant set of options always available to the user.

  47. User Form Options (cont’d) • There are some general properties that apply to the user form as well as to most controls. • These include general formatting properties such as • Font • BackColor • ForeColor • As we have mentioned already some other common properties found for most if not all controls are Name, Caption, and Visible. • There are three other important common properties used with most/all controls, they are • TabIndex • TabStop • Locked

  48. User Form Options (cont’d) • TabIndex and TabStop control the tabbing order in which the user can move through controls on the user form with the Tab button of their keyboard. • The TabIndex property takes a numerical value from 1 to the number of controls on the user form; 1 being the first item visited when Tab is pressed. • The TabStop property can be set to True or False depending on whether or not you want the user to be able to select a certain control.

  49. User Form Options (cont’d) • The Locked property takes True or False values and allows you to prevent the user from entering or changing any values of a control. • The control will appear darkened and its value, if any, will be displayed, but the user will not be able to modify the control value if the Locked property is set to True. • The ControlTipText property allows us to enter some text to serve as brief instructions to the users to explain what a particular control will do. • This text will appear when users bring their cursor over the control. • This property is available on all form controls.

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

More Related