YO$22\_$Excel$\_$BU$04\_$PS$1\_$Uniforms

Project Description:

The staff at Bright Nails Salon and Spa are required to wear uniforms every day. While the hotel provides uniforms each year to staff members, occasionally they want to order items in addition to what is provided to them. One of the recent interns at the salon created a spreadsheet order form, but never finished it$.$ You will finish working on it so it can be distributed to the staff. You will add data validation rules, a macro, and make it look less like an Excel spreadsheet and more like an online order form so it is easy to use.

Steps to Perform:

Step Instructions Points Possible

$1$ Start Excel. Download and open the file named Excel$\_$BU$04\_$PS$1\_$Uniforms.xlsx$.$ Grader has automatically added your last name to the beginning of the file name. $0$

$2$ On the OrderForm worksheet, locate and correct the circular reference. $9$

$3$ Select cell C$10,$ and enter a data validation rule to look up the department name from the cell range named Departments on the Options worksheet. Enter the Input Message title Department

Enter the input message Select a department from the list. $($Include the period$)$

Enter an Error Alert title Error

Enter the error message Invalid department. $($Include the period$)$

Select cell C$10,$ and select Front Desk. $10$

$4$ Select cell range C$13$:C$18,$ and enter a data validation rule to look up the top options from the cell range named TopOptions on the Options worksheet. Leave the input message and error message blank.

Select cell C$13,$ and select Vest.

$10$

$5$ Select cell range C$19$:C$24,$ and enter a data validation rule to look up the bottom options from the cell range named BottomOptions on the Options worksheet. Leave the input message and error message blank. $8$

$6$ Select cell range C$25$:C$30,$ and enter a data validation rule to look up the other options from the cell range named OtherOptions on the Options worksheet. Leave the input message and error message blank. $8$

$7$ Select cell range D$13$:D$30,$ and enter a data validation rule to lookup the size options from the cell range named Sizes on the Options worksheet. Leave the input message and error message blank.

In cell D$13,$ select Large.

In cell E$13,$ type $211$

$8$ Select cell A$13,$ and insert a hyperlink to the defined name Tops. Add a ScreenTip Go to Top Options

Select cell A$19,$ and insert a hyperlink to the defined name Bottoms. Add a ScreenTip Go to Bottom Options

Select cell A$25,$ and insert a hyperlink to the defined name Other. Add a ScreenTip Go to Other Options $12$

$9$ If necessary, add the Developer tab to the ribbon. Record a macro named ClearForm with the shortcut key F and the description To clear the form for another order. $($including the period$).$ Clear the contents from cells C$6$:C$7,$ C$10,$ C$13$:E$30$ and C$33.$ Click cell C$6$ and stop recording the macro. Undo the changes you made while recording the macro.

Open the macro in the Microsoft Visual Basic Applications window, copy the code, then close the window. Paste the macro onto the Macro worksheet beginning in cell A$1.$

Mac users, use the Text Import Wizard from the Paste menu to remove any delimiters, if necessary. $10$

$10$ Hide the Macro worksheet. $2$

$11$ Modify the workbook for easier navigation:

Hide the gridlines on the OrderForm worksheet.

Hide the horizontal scroll bar on the OrderForm worksheet.

Hide row and column headings on the OrderForm worksheet.

On the OrderForm worksheet, unlock the cells required for data entry $($the same cells the macro clears$).$

Protect the OrderForm worksheet $($do not use a password$).$ Deselect the Select locked cells checkbox.

$20$

$12$ Save and close the Excel$\_$BU$04\_$PS$1\_$Uniforms.xlsx workbook. Exit Excel, and then submit your file. $($Be sure you are not submitting a macro$-$enabled workbook.$)0$