Working with option button, check box, and list box controls

1. 13 Working with option button, check box, and list box controls Visual Basic for Applications

2. 13 Objectives • In this unit, you will learn how to: • Create and use option button, check box, and list box controls • Create an option group • Select the default option button and list box item • Use the Sort method in Excel • Use the AddItem method to display items in a list box • Use the QueryClose event in Word • Use the List Box Control’s Column propertyin Access

3. 13 Concept Lesson:More Dialog Box Controls • Option button, check box, and list box controls provide a means by which the user can enter data into a dialog box Exhibit 13-1: The Calculate Price dialog box

4. 13 The Option Button Control • You use the option button control in a dialog box when you want to limit the user to only one choicein a group of two or more related and mutually exclusive choices • In all Microsoft Office applications except Access, you can create a group of option buttons, called an option group, by setting each button’s GroupName property to the same value • You create an option group in Microsoft Access by placing the option buttons in an option group control, which is a rectangular control in Access that acts as a container for option button, check box, and toggle button controls

5. 13 The Option Button Control • The minimum number of option buttons in a group is two, because the only way to deselect an option button is to select another option button • The selected button, referred to as the default option button, should be the button that represents the user’s most likely choice • In all Microsoft Office applications except Access, you select the default option button by setting the button’s Value property to the Boolean value True • In Access, you set the option group control’s DefaultValue property to a number that represents the option button in the group that you want to be the default button

6. 13 The Check Box Control • You use the check box control to allow the user to select any number of choices from a set of one or more independent and nonexclusive choices • Any number of check boxes in a dialog box can be selected at the same time • The Windows standard is to have all check boxes unselected when the dialog box first opens

7. 13 The List Box Control • A list box control can be used to display a set of predefined choices from which the user can select only one of the choices • This type of list box is called a single selection list box • When a user selects an item in a single selection list box, the item is assigned automatically to the list box control’s Value property • The ListIndex property keeps track of the various items in a list box • The Windows standard is to display, in the list box, a minimum of three items and a maximum of eight items at a time

8. 13 Summary To limit the user to only one choice in a group of related and mutually exclusive choices: • Use either the option button control or the list box control • The minimum number of option buttons in a group is two; the recommended maximum is seven • Display, in a list box, a minimum of three items and a maximum of eight items at a time

9. 13 Summary To create a group of option buttons: • In all Microsoft Office applications except Access, set each button’s GroupName property to the same value To select the default option button: • In all Microsoft Office applications except Access, set the option button control’s Value property to the Boolean value True

10. 13 Summary To select the default list box item: • In all Microsoft Office applications except Access, set the list box control’s ListIndex property to a number that represents the item’s location in the list • In Access, assign the item, entered as a string, to the list box’s Default Value property

11. 13 Summary To determine which item is selected in a list box that allows one selection only: • The list box control’s ListIndex property contains a number that represents the item’s location in the list • In all Microsoft Office applications except Access, the list box control’s Value property contains the selected item

12. 13 Summary To allow the user to select any number of choices from a set of one or more independent and nonexclusive choices: • Use the check box control. Check boxes typically are unselected when the dialog box first opens To select a check box: • Set the check box control’s Value property to the Boolean value True

13. 13 Excel Lesson:Viewing the Sales Workbook • Before creating the custom dialog box and macro that you will use to calculate a salesperson’s total and average sales, view the workbook

14. 13 The Custom Dialog Box You Will Complete in this Lesson Exhibit 13-2: A custom dialog box

15. 13 Coding the Form’s Initialize Event • A form’s Initialize event occurs after the form is loaded into memory but before it is shown onthe screen • In the Initialize event, you enter code that prepares the form for use Exhibit 13-3: The pseudocode for the Sales Calculator form’s Initialize event

16. 13 Using the Sort Methodto Sort a Range • You can use the Range object’s Sort method to sort a range of values in either ascending or descending order Exhibit 13-4: The basic syntax and examples of the Range object’s Sort method

17. 13 Using the Sort Methodto Sort a Range • You can sort a range of data based on the values stored in the one, two, or three different columns, specified in the Sort method’s Key1, Key2, and Key3 arguments • You use the Header argument, which can be set to the intrinsic constants x1Yes, x1No, or x1Guess, to specify whether the first row in the sort range contains column headers

18. 13 The AddItem Method • You use the AddItem method to specify the items you want displayed in a list box control • The AddItem method also allows you to specify the position where the new item will be placed within the control’s list • If no data appears in your list box, and the Total sales option button is not selected, close the dialog box

19. 13 Examples of Usingthe AddItem Method Exhibit 13-5: Some examples of using the AddItem method

20. 13 The AddItem Method • You can use the list box’s Column Widths property, which controls the width of each column in a list box, to remove the horizontal scroll bar, and you can use either the list box’s handles or its Width property to decrease the width of the list box

21. 13 Pseudocode for the Calculate Button’s Click Event Procedure Exhibit 13-6: The pseudocode for the Calculate button’s Click event procedure

22. 13 Coding the CalculateSales Macro To code the CalculateSales procedure, then test the macro: 1. Open the Project Explorer window. Open the Module1 module’s Code window, then view the code template for the CalculateSales procedure 2. Press the Tab key. Type ‘display custom dialog box and press the Enter key, then type frmsalescalc.show and press the Enter key 3. Save the workbook, then return to Excel 4. Run the CalculateSales macro. Use the Sales Calculator dialog box to calculate the total sales for salesperson 105. The label control should show the total sales are $11,847.00 5. Close the dialog box. Save the workbook, then exit Excel

23. 13 Word Lesson:Viewing the Schedule Document • Before creating the custom dialog box and macro that will be used to create and print the schedule, view the document • To view the document: 1. Start Microsoft Word 2. Open the T13-WD-1 (T13-WD-1.doc) document, which is located in the Tut13\Word folder on your Data Disk. Click the Enable Macros button, if necessary, then save the document as Schedule.

24. 13 Coding the UserForm’s Initialize Event • A form’s Initialize event occurs after the form is loaded into memory but before it is shown onthe screen • In the Initialize event, you enter code that prepares the form for use Exhibit 13-7: The pseudocode for the Scheduler form’s Initialize event procedure

25. 13 Pseudocode for the EnterButton’s Click Event Procedure Exhibit 13-8: The pseudocode for the Enter button’s Click event procedure

26. 13 Coding the UserForm’s QueryClose Event • A form’s QueryClose event occurs before the form is removed from the computer’s memory, and it typically is used to perform last-minute tasks, such as verifying that the user saved the current data

27. 13 Coding the CreateSchedule Macro • The Schedule document already contains the code template for the CreateSchedule macro procedure

28. 13 Access Lesson:Viewing the Database • Before modifying the dialog box, view the records contained in the database’s AdjunctFaculty table • Also view a new table, named CourseNumbers, that contains the number of each CIS course

29. 13 Modifying the Locate Button’s Click Event Procedure Exhibit 13-9: The pseudocode for the Locate button’s Click event procedure • Before displaying the appropriate report, the Click event procedure will need to determine which option button is selected in the option group • To modify the Locate button’s Click event procedure, use the steps on pages 854 to 856 of the textbook

30. 13 Code to Delete the cmdLocate Control’s Click Event Procedure Exhibit 13-10: The code to delete in the cmdLocate control’s Click event procedure