Excel Assignment 6

maxwell.zip

Home >Computer Science homework help >Excel Assignment 6

Maxwell.xlsm

Documenation

Maxwell Scientific
Author
Date
Purpose	To record orders from Maxwell Scientific and update the inventory

Order Form

Maxwell Scientific
Order Form
	Item to Order	Send To
	Order ID	Name
	Stock ID	School Name
	Stock Description	Street
	Item Price	City
	Items to Order	State
	Order Price	ZIP Code
	Sales Tax
	Total Cost

Submit Order

Inventory

Maxwell Scientific
Inventory
Stock ID	Price	Stock Description	Items in Stock	Items Ordered	Remaining Stock
V1010	19.95	Physics for Poets I DVD	51	0	51
V1011	19.95	Physics for Poets II DVD	42	0	42
V1012	19.95	Physics for Poets III DVD	39	0	39
V1013	29.95	Physics for Poets IV DVD	18	0	18
V1014	19.95	Einstein DVD	68	0	68
V1015	19.95	Newton DVD	72	0	72
V1016	19.95	Maxwell DVD	45	0	45
K1021	5.95	Magnets kit	52	0	52
K1022	10.95	Colors kit	38	0	38
K1023	5.95	Minature Tornado	71	0	71
K1024	9.95	Optics kit	31	0	31
K1025	19.95	Electronics kit	48	0	48
E1020	29.95	Capacitors	29	0	29
E1021	39.95	Transformers	34	0	34
E1022	29.95	Resistors	39	0	39
E1023	9.95	Large cell battery	28	0	28
E1024	39.95	Logic circuits	14	0	14
E1025	9.95	Circuit board	25	0	25
E1026	9.95	Photo-electric cell	19	0	19
E1027	9.95	CDC cell	25	0	25
E1028	19.95	30' wire spool	44	0	44
E1029	9.95	Switches	48	0	48
O1000	9.95	Prism (sm)	55	0	55
O1001	9.95	Prism (med)	51	0	51
O1002	13.95	Prism (lg)	29	0	29
O1003	19.95	Lens kit	34	0	34
O1004	29.95	Focal reducer	8	0	8
M0501	19.95	Optics Made Easy book	145	0	145
M0502	19.95	Electronics Made Easy book	149	10	139
M0503	19.95	Science Made Easy book	161	0	161
M0504	19.95	Astronomy Made Easy book	139	0	139
M0505	29.95	Introduction to Circuits book	121	0	121
M0506	29.95	Newton & the Prism book	115	0	115
M0507	29.95	Logic circuits book	91	20	71

Order History

Maxwell Scientific

Order History

Order ID

Stock ID

Stock Description

Price

Items

Order Price

Sales Tax

Total Cost

Name

School Name

Street

City

State

ZIP Code

M0507

Logic circuits book

29.95

599

11.98

610.98

Alan Wilkes

Lincoln High School

55 Hampton Rd.

Glendale

91201

M0502

Electronics Made Easy book

19.95

199.5

3.99

203.49

Drew Dawson

Canope Middle School

414 Oakburn Ave.

Santa Clara

94086

Maxwell Instructions.docx

(Steps 1 and 2 are skipped as it regards just the file name and location)

3. In the order form worksheet record a macro named Add_Order that performs the following actions so that a new row in the Order history worksheet will score orders entered from the Order Form:

a. Select the Order History worksheet

b. select the entire fifth row of the worksheet

c. Insert a new sheet row in the worksheet

d. Using the Format Options button apply the format Same as Below option so that the new row adopts the same format as the row below it.

e. select the Order from the worksheet.

4. In the visual basic editor change the name of the VBA project to Customer_Orders.

5. In the Add_Order sub procedure directly above the closing end sub line insert the following BA commands:

a. Set the value of cell A5 in the Order History worksheet equal to the value of cell C5 in the Order Form worksheet.

b. Set the value of cell B5 in the Order History worksheets equal to the value of cell C6 in the Order Form worksheet.

c. Continue to set the value of the cells in the fifth row of the Order History equal to their corresponding entries in the Order Form worksheet. Your last command should set the value of cell N5 in the Order History sheet equal to cell F10 in the Order Form sheet.

6. After an order is submitted and stored in the Order History log in should be cleared from the Order Form sheet. Using the clearcontents method of the range object, add a VBA command to the Add_Order procedure to clear the consents of the previous order using the parameter value “C5:C6, C9, F5:F10”

7. In the Excel workbook delete the blank fifth row in the Order History worksheet.

8. Save the workbook.

9. Assign the Add_Order macro to the submit Order button on the Order Form worksheet.

10. Use the macro you wrote to add the orders showing in Figure C-25 to the Order History worksheet. Verify that the Order Form is cleared after each order is submitted.

Figure C-25:

Sample orders for Maxwell Scientific

Order Form Items	Order 1	Order 2
Order ID	R3	R4
Stock ID	E1027	01000
Items to Order	20	15
Name	Robert Blaska	Linda Nuland
School Name	Elmwood High school	Country Day
Street	100 North Avenue	45 Ridge Lane
City	Nampa	Woodburn
State	ID	OR
Zip Code	83686	97071

11. Check the Order History sheet and verify that the orders were inserted at the top of the history log. If the macro didn’t work, close the workbook without saving it and then start it again.

12. Unlock the cells in range C5:C6 cell C9 and the range F5:F10 in the Order Form worksheet and then protect the sheet so that those unlocked cells can be edited.

13. Save the workbook.