[guispeak] ms office 2000 issues
- From: "Marvin Hunkin" <marvkin@xxxxxxxxxxx>
- To: <guispeak@xxxxxxxxxxxxx>
- Date: Thu, 17 Feb 2005 10:50:21 +1100
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.
Other related posts:
- » [guispeak] ms office 2000 issues
