Hi. doing the software applications subject at my college. up to the excel section. now running into problems using jaws 5.10, windows 2000, and ms office 2000. using excel 2000. i am to use a set of ovals, and draw option buttons, using the forms toolbar. but the problems i am having is that: i try to select the cell, go to the form toolbar, and try to go to form properties. but it says it is greyed out. and there is no keyboard shortcut, using the left and right slash keys, and route jaws cursur to get to the form control edit menu, so i can then set the form control properties to set the control, cell to link it to. and then create three macros, to do with a pizza form. to have the three option buttons, named family, large and small. then colour the first button, bright green, and the other two yellow. and when i do get to the edit text, jaws has problems seeing the edit field. am able to edit, but it works for the first option button. but for other buttons, the text is split, and still having problems reading with jaws. tried to reclassify the control to multiline edit , but no luck, and tried widening the collumsn. so any one if you have done this with office 2000, then let me know. do i need to use the prompt creator, or maybe create a hot key? would that work? tried to do the lesson the first part about six or seven times, but started again. very frustrating. will paste the document for the lesson, so you can get an idea of what i am up against. if any one has got any ideas, suggestions, tips, tricks, then let me know. even if i have to do the damn thing in vba, then send me the code to do this. on another topic, when i was doing the word section, jaws was not reading the edit window in the customize option of ms office 2000. when i create a macro, want to create a button, and atatch it to a toolbar. i create the macro in word, go to customize, and go to macro, find my macro, and set it in which document. then i route the jaws cursur, to the modify selection, but nothing happens, and when i hit the right slash key, only what's this appears. so jaws is not reading the say the name option in the menu. so cannot change the text of the macro from say NewProject.Macros.DisplayLetterHead and just have the text letter head say in a toolbar. do i need to reclassify this control to a multi line edit box? let me know. or if you have written this module in code, then let me know. Microsoft Excel 2000 ICBITU126B Used Advanced Features of Computer Applications LESSON 6 This Lesson will practice functions, templates, form controls and macros. Instructions for creating a Pizza Order Form in Microsoft Excel TABLE OF CONTENTS Introduction.. 42 Instructions. 43 Step 1 - Create Pizza Shapes. 43 Step 2 - Draw Option Buttons. 43 Step 3 - Record a Macro to Colour the Pizzas. 44 Step 4 - Create Pizza Table. 46 Step 5 - Create a Combo Box on the Pizza Form to select the type of Pizza 47 Step 6 - Draw Check Boxes for Extras. 48 Step 7 - Record a Macro to move to the Receipt Form area. 49 Step 8 - Creating the Order Form.. 51 Step 9 - Adding the Extras to the Order Form.. 52 Instructions for Adding a Message Box and Input Box to the Worksheet.. 53 Step 10 - Adding an Input Box and a Message Box to the Order Form Error! Bookmark not defined. Instructions for creating a Pizza Order Form in Microsoft Excel Introduction These instructions outline the process for creating a Pizza Order form in Microsoft Excel. The spreadsheet file uses a combination of Form Controls, macros and functions. The instructions are written for the experienced user and provide a logical sequence to create the Pizza Order Form. Subsequently, minor details have been omitted based on the assumption that they are unnecessary. Three worksheet will be used for this activity. The Control Sheet- where the user can select a Family, Large or Small pizza, with extras. The Data Sheet - where data such as types of pizzas and their price, and the extras and their price will be stored. The Receipt - where a completed Customer Order will be created, which may be printed and handed to the customer. Instructions Open a New Blank Excel Workbook. Rename the sheets: Control, Receipt and Data Step 1 - Create Pizza Shapes Using the Control Sheet, carry out the following: Create three pizza shapes in the current screen area (for example, the current screen may be A1 to I16). The pizza shapes are to represent a family, large and small pizza. Tip: Use the Oval icon on the Drawing Toolbar to create the Pizza Shapes. Holding the Shift key whilst drawing the oval will result in a circle. Step 2 - Draw Option Buttons ? Open the Forms Toolbar. ? Beneath the "Family" circle, draw an Option Button (from the Forms Toolbar). Please note that the first Option Button drawn is Option Button 1 and will return a 1 to the Cell Link, the second is Option Button 2 and will return a 2 to the Cell Link. Excel remembers the sequence in which these buttons were drawn. ? Right click the Option button, Edit the Text to say Family ? Right Click the Option Button again and select Format Control. Link the Option button to the Data Sheet, Cell H1. ? Repeat this process for the "Large" and "Small" pizzas but you do not have to Format the Control again. Each option button will link to the same cell. ? In cell G1 of the Data Sheet, type the word Size. Then Define the name for cell H1 as Size (This is the Option button cell link). ? This will later be used in an =OFFSET function. (OFFSET returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.) Step 3 - Record a Macro to Colour the Pizzas Record a macro to colour the Pizzas - Turn on the macro recorder, and name the macro Family. Colour the family pizza bright green, and then the remaining two pizzas light yellow. After colouring the pizzas click in Cell A1 and stop recording. Go to Tools, Macro, Macro, select the macro named Family and then click on the Edit button to edit the macro in the macro editor. The macro code appears similar to that below: Sub Family() '' Macro1 Macro ActiveSheet.Shapes("Oval 2").Select Selection.ShapeRange.Fill.ForeColor.SchemeColor = 11 Selection.ShapeRange.Fill.Visible = msoTrue Selection.ShapeRange.Fill.Solid ActiveSheet.Shapes("Oval 3").Select Selection.ShapeRange.Fill.ForeColor.SchemeColor = 43 Selection.ShapeRange.Fill.Visible = msoTrue Selection.ShapeRange.Fill.Solid ActiveSheet.Shapes("Oval 4").Select Selection.ShapeRange.Fill.ForeColor.SchemeColor = 43 Selection.ShapeRange.Fill.Visible = msoTrue Selection.ShapeRange.Fill.Solid Range("G14").Select End Sub ? Copy this macro and paste it twice. For example, copy all the macro code for the Family macro, click under where it says End Sub and then paste. Repeat this process again. Rename the two pasted macros, the original macro is Family(), the second rename to Large() and the third rename to Small(). ? The colour /number sequence also needs to be edited. This will result in the pizza selected becoming the brightest colour. The Family macro needs to show the colour/number sequence of 11, 43, 43. The Large macro needs to show the colour/number sequence of 43, 11, 43. The Small macro needs to show the colour/number sequence of 43, 43, 11. ? Close out of the Visual Basic Editor by clicking on the close button at the top right of the window. ? Click right on the Option Button named Family and Assign the Family macro to the Option Button. Repeat this process for each of the option buttons, eg assign the Large macro to the Option Button named Large, etc. Step 4 - Create Pizza Table Go to the Data Sheet, carry out the following: Enter the following information to create a Pizza Table, starting at cell A1. Type Family Large Small Pepperoni $19.00 $15.00 $13.00 Hawaiian $18.00 $14.50 $13.50 Mexican $15.00 $12.00 $11.00 Supreme $20.00 $15.50 $14.00 Tip: This table will be used to select the Pizzas for a Combo Box Form Control on the Control Form, and also used in various OFFSET functions on the Receipt Form. Step 5 - Create a Combo Box on the Control Sheet to select the type of Pizza From the Forms Toolbar, draw a Combo Box button. After selecting Format Control, the window below should appear: The Input Range is selected from the Pizza Table on the Data Sheet - you select the various types of pizzas you have entered in your table in Column A (e.g. Pepperoni, Hawaiian, Mexican, Supreme). The Cell Link is to the Data Sheet Cell H2. The link will enter the number for the type of pizza selected. For example, the first pizza in the list will return a 1. After formatting the control, click on OK. Go to cell G2 of the Data Sheet and enter the label: Type. Define a range name for cell H2 of the Data Sheet named Type. This range will also be used in OFFSET functions on the Receipt Form. Check the combo box on the Control Sheet to see if you can select a pizza and subsequently return a number in cell H2 of the Data Sheet. Step 6 - Draw Check Boxes for Extras From the Forms toolbar, draw a check box for each of the extras available, for example: anchovies, extra cheese, BBQ sauce, etc. These check boxes can be drawn under or near your combo box. Format each check box, i.e. right click on the check box and select Format Control. Link each check box to cell in Column B of the Data Sheet under the table of pizzas. When the check box is ticked, it will return a TRUE value in the Cell Link (see diagram below) or a FALSE if it is not ticked. Enter Labels in Column A to correspond to the extras. Step 7 - Record a Macro to move to the Receipt Form area Record a macro named Receipt. ? Turn on the macro recorder and name the macro Receipt, then record the steps to go to Receipt sheet, then Stop recording. ? Return to the Control Form. ? From the drawing toolbar, draw a square button somewhere on the Pizza Form. Click right on this button and Assign the Receipt macro. Again right click on this button and Edit the Text to See Receipt. The Pizza Form should appear similar to that shown on the following page: Step 8 - Creating the Receipt Go to the sheet named Receipt. In cell A1 give the Receipt a title, like "Your Name" Pizza Parlour (eg Fred's Pizza Parlour). Format the title to look impressive. In Cell A3 enter the following formula: (N.B. Remember you can use your mouse to click on the required cells in a formula) =CONCATENATE("You have ordered a ",OFFSET(Data!A1,0,Size)," ",OFFSET(Data!A1,Type,0)," Pizza)) The =CONCATENATE joins all the parts together. The =OFFSET function returns values for the size of pizza, the type of pizza, and the required text and spaces are between the quotation marks. The =OFFSET function requires a starting point, which is cell A1 of the Data Sheet (the start of the pizza table), it then requires how many rows you need to come down which is determined by the cell link for the Type of pizza (the combo box cell link), and then it requires the number of columns to go across which is determined by the Size of pizza (the option button cell link). The formula should return the following text in the Receipt worksheet : You have ordered a Small Pepperoni Pizza ? In Cell B3, type: Cost ? In Cell C3, enter the formula: =OFFSET(Data!A1,Type,Size) ? (This should return the cost of the pizza selected) ? Format cell C3 to currency. Step 9 - Adding the Extras to the Receipt Include the extras on the Receipt, e.g. Anchovies, Extra Cheese, BBQ Sauce In Cell B4 of the Receipt type: Anchovies In Cell B5 type: Extra Cheese In Cell B6 type: BBQ Sauce To get the cost of Anchovies if it is ordered, enter the following formula in C4: IF(Anchovies=TRUE,Data!C?,"") The ? has been used as the cell reference will depend on where the price of Anchovies is on your worksheet. Repeat the same formula, being careful to pick up the correct cell address for Extra Cheese and BBQ Sauce. On the Receipt add a label and formula for the Total Cost. In cell B7 type: Total Cost In Cell C7 use the =SUM function (autosum) to total the cost. You may wish to format the Order Form to appear more professional, i.e. colours, borders, fonts, turning off gridlines, etc. Record a macro to move from the Receipt Form to the Control Form, and assign this macro to a button. You will now add a input box to the workbook to add the customer's name to the Receipt and a message box asking if they would like Coke or Garlic Bread with their order. Instructions for Adding an Input Box and a Message Box to the Worksheet An Input Box will be added to the worksheet which will prompt for the customer's name. A Message Box will then prompt whether the customer would like garlic bread or coke with their order. Both boxes will be created in the visual basic macro editor. ? Firstly, on the Data sheet, enter Coke in Column A under BBQ Sauce, and add the price for Coke ($2.00) in Column C under the price of the BBQ sauce. In Column B between the label Coke and the price, define the name of this cell as Coke. ? Under the entry for Coke, add Garlic Bread and its price ($3.00). Also define the name of the cell in Column B as GarlicBread (remember no spaces in a range name). ? Go to the Receipt Form. On the Receipt form, under the name of your Pizza shop (you may need to insert a row) enter a label in column A which says: Customer Name ? In column B adjacent to this label, define the name of the cell as Name ? Add the text Coke and Garlic Bread to your Receipt, below the other extras. N.B. You may need to insert two rows above the Total Cost entry. The Receipt worksheet is now ready. You will now create the Input Box and the Message Box. The Message Box and Input Box will now be added to the Receipt macro. Subsequently, when you click on See Receipt, the Input Box will prompt for the customer's name and the Message Boxes will prompt for Coke or Garlic Bread. ? Edit the Receipt macro. Select Tools, Macro, Macro, select Receipt and then click on Edit. (Alternatively ALT F8 will take you to the Macro window.) ? You should now be in the Visual Basic Macro editor. Under the macro name Sub Receipt() and under any green lines of text (which are comments), and before the existing code: Type the following code:. ' customername = InputBox("May I have your name please", "The Pizza Shop") Range("name") = customername response = MsgBox("Would you like Coke with your pizza?", vbYesNo, "The Pizza Shop") If response = vbYes Then Range("Coke") = True Else Range("Coke") = False End If response = MsgBox("Would you like a Garlic Bread with your pizza?", vbYesNo, "The Pizza Shop") If response = vbYes Then Range("GarlicBread") = True Else Range("GarlicBread") = False End If The existing following code may be similar to: Sheets("Control").Select Range("A2").Select End Sub ? Close out of the Visual Basic Macro Editor and text your macro. ? Test the macro by clicking on the See Receipt button. ? Now improve the appearance of the Control Sheet and the Receipt Form by using various formatting. Go to Tools, Options, View and turn off options such as Formula bar, Row & column headers, Scroll bars, and Sheet tabs Try Protecting the workbook. Remember to unlock any cells which need to change by going to Format, Protection, Unlock . The cell which need to change include the cell links for the Option Buttons, check boxes, and Combo Box, the customer name, the size and type of pizzas and prices ? Show your instructor the finished product. Summary In this activity, you have used a range of functions, form controls, macros, formatting and document protection. In the following activity you will use the Haveago Manufacturing workbook and assign form controls and macros to a control sheet. To ensure the activity notes correspond to the Excel workbook, you will be asked to open the Haveago Manufacturing Completed file from the network library. cheers Marvin.