Roberta Olson is a dispatch manager for Milwaukee Cheese, a large cheese and dairy supply company operating in the Midwest. One of Robertas jobs is to provide the shipping assignments to the company drivers among 27 Wisconsin cities. It is company policy that no driver logs more than 350 miles in a single day driving from one distribution center to the next. You will help Roberta develop an Excel application for entering driving assignments that fulfill company policy. Robertas worksheet includes a Driving Form worksheet in which you will enter commands to store each leg of a driving itinerary. The legs will then be stored in the Itinerary table of the Driving Itinerary worksheet. The distances between the 27 cities are stored in the Distance Table worksheet. Distances will be automatically calculated for you. In this application, you will combine validation tests within a single cell using a custom valida-tion formula. For a driving leg to be valid it should start and end in one of the 27 cities, and the total driving distance should not exceed 350 miles.

Open the NP_EX_12-4.xlsm workbook located in the Excel12 > Case2 folder included with your Data Files. Save the workbook as NP_EX_12_Cheese in the location specified by your instructor.

2. In the Documentation worksheet, enter your name and the date.

3. In the Driving Form worksheet, you will enter individual legs of a driving itinerary. The mileage between starting and ending cities is calculated in cell F6. Currently that cell displays an error value because no cities have been specified in cells D6 and E6. Revise the formula in cell F6 so that it displays a blank text string in place of an error value.

4. This form will use the AutoComplete feature to fill in the city names in cells D6 and E6. There is no need to display the city names in rows 7 through 33 for AutoComplete to work. Hide rows 7 through 33 in the worksheet so that content doesnt distract the user.

5. Unlock cells D6 and E6.

6. The data in cells D6 and E6 have two validation rules: The city names must be included in the list of cities. The total of the driving mileage in cell F6 and the value stored in the dist range name should be less than or equal to 350. In the range D6:E6, create a custom validation rule using the following formula: =AND(COUNTIF(cities,D6)=1,SUM($F$6,dist)<=350)

7. If the validation rule is violated, display a warning box with the title Invalid Data and the message You either mistyped the city name or adding this leg will result in a total driving distance exceeding 350 miles. (including the period).

8. Hide the Distance Table worksheet.

9. Protect the workbook. Do not specify a password.

10. Protect the contents of the Driving Form and Driving Itinerary worksheets, allowing users to only select locked and unlocked cells. Save the workbook.

11. In the Driving Form worksheet, enter Milwaukee as the starting city of the first leg in cell D6, and then enter West Allis as the ending city in cell E6. Verify that the distance between the two cities is 7 miles.

12. Save the workbook.

13. Use the macro recorder to create a macro for this workbook named Add_Leg with the description Add a leg to the driving itinerary.

14. Start the recorder, and then perform the following tasks: a. Go to the Driving Itinerary worksheet and unprotect the sheet. b. Press F5 and go to the travel_end cell. c. Insert a new sheet row above the travel_end cell. (Hint: On the Home tab, in the Cells group, click the Insert arrow, and then click Insert Sheet Rows.) d. Go to the Driving Form worksheet and copy the values in the range C6:F6. e. Return to the Driving Itinerary worksheet and use the Paste Special command to paste the values and number formats into the active cell of the worksheet. f. Protect the Driving Itinerary worksheet. g. Press F5 and go to the travel_end cell again. h. Unprotect the Driving Form worksheet. i. Copy the value in cell E6 and paste that value into cell D6. j. Click cell E6, and then press DEL to clear the contents of the cell. k. Protect the Driving Form worksheet. l. Stop the recorder.

15. Unprotect the Driving Form worksheet and insert a macro button in the range D35:E37 to play the Add_Leg macro. Change the label of the macro button to Add Leg to the Itinerary. Protect the Driving Form worksheet again.

16. Use the data form to enter the following legs into the driving itinerary: West Allis to Madison, and then Madison to La Crosse.

17. Verify that when you try to enter a fourth driving leg of La Crosse to Marshfield, the application warns you that you are about to exceed the allowed driving distance. Do not enter Marshfield as the last leg of the trip. Instead enter Eau Claire as the last leg.

18. When a macro switches between worksheets, the quick jump from one sheet to another can be distracting. To correct this problem, edit the Add_Leg sub procedure in the Visual Basic for Applications editor. a. Directly after the initial comment section at the top of the sub procedure insert the following command to turn off screen updating while the macro is running: Application.ScreenUpdating = False b. Directly before the closing End Sub command at the bottom of the sub procedure, insert the following command to turn screen updating back on: Application.ScreenUpdating = True

19. Close the editor and then return to the workbook.

20. Save the workbook, and then close it.