P6 Practice 6 Create a dynamic interface to evaluate upcoming employee travel based on client location and number of nights. Travel costs to client locations are given. This application should be able...

I have a VBA homework due tonight, 3 sheets of excel file


P6 Practice 6 Create a dynamic interface to evaluate upcoming employee travel based on client location and number of nights. Travel costs to client locations are given. This application should be able to filter for given location to find which employee will be there maximum number of nights. The application should also be able to calculate total costs for a given employee based on travel costs and number of nights. 2 Questions #1: Create a button that filters table by client location and finds name of employee who will stay the maximum number of nights. Ask user for a city name. In Excel, name first cell of employee table, then in VBA use a range variable to record current employee table (with labels). Filter the table for the given city name. Copy/paste the filtered table and record the pasted table in a range variable. Calcualte the maximum number of nights from the filtered table. Find the name of the employee who will stay the maximum number of nights. Hint: Use Application.WorksheetFunction.Match and Range().Cells. Delete the pasted range and select the first cell of the employee table. Display a message: "Employee name will be in City Name the max number of nights: max number" with title "Max Nights in City Name". Remove filter from employee table. #2: Create a button that calculates the total travel cost for a given employee. Ask the user for an employee name. Use a range variable to record current employee table (with labels). Look up the client location and number of nights for the given employee name. Then look up the travel cost for the client location in the travel cost table. Hint: You can name this table in Excel. Calculate the total travel cost based on the location cost and number of nights (assume $100/night hotel cost). Calculate the return date based on today's date and the number of nights. Highlight the current row of the table for the given employee. Display a message: "Trip Name: " first initial of first and last name "-" first three letters of location in upper case. Next line total cost. Next line return date. Hint: Ucase() function for upper case; Format(x, "Currency") for total cost; InStr(text to look in, text to find) to find first initial of last name. Upcoming Travel EmployeeClient LocationNumber of Nights A. BilakbarAtlanta5 C. DenversCharlotte3 D. EdwardsBaltimore1 E. FernandezRichmond2 F. GarciaArlington1 G. HoiCharlotte1 P. QuiBaltimore2 S. TiagoArlington4 U. VanceAtlanta4 V. WilsonRichmond4 W. XieArlington2 X. YenBaltimore5 Y. ZeniyaCharlotte4 Given Travel Costs (round-trip) Client LocationTravel Cost Arlington$262.50 Atlanta$450.00 Baltimore$375.00 Charlotte$150.00 DC$300.00 Richmond$187.50 Filter Location and Find Max Nights Calculate Employee Travel Cost and Return Date P7 Practice 7 Create a dynamic interface to allow users to either search current scheduled travel or add new trips. Application should be able to look up information or add new information. Should include error checking. 1 Question #1: Allow user to either lookup trip information by employee name or add new trip information to table. Ask user for menu option: 1 to lookup, 2 to add new. Make sure menu option is valid. In Excel, name starting cell for travel table and name entire location/cost table. In VBA, record range of entire scheduled travel table (with labels). Ask user for employee name. If user wants to lookup info: check if employee name is in first column of travel table. If not, give error message. Otherwise, lookup location, nights, and cost info and give user report message. If user wants to add new trip: check if employee name is already in first column of travel table. Hint: check If Not Range().Find() Is Nothing. If already scheduled, give error message. Otherwise, ask for new location and check that it is in first column of location/cost table. Ask for number of nights and make sure positive number. Calculate total cost = travel cost + nights * $100/night. Output new trip information to new row in table. Hint: Use With start range .End(xlDown) and Offset to new row. Scheduled Travel EmployeeClient LocationNumber of NightsTotal CostClient LocationTravel Cost F. GarciaArlington1$562.50Arlington$262.50 S. TiagoArlington4$462.50Atlanta$450.00 D. EdwardsBaltimore1$675.00Baltimore$375.00 J. JonesArlington2$462.50Charlotte$150.00 M. JaneDC4$700.00DC$300.00 Richmond$187.50 Employee Travel: Lookup or Add New Trip P8 Practice 8 Create dynamic interface to allow user to modify number of nights in scheduled trip. User can lookup trip info and update nights which should also update cost. 1 Question #1: Allow user to update number of nights for scheduled trip (assume only one trip scheduled per employee). In Excel, name starting cell of travel table and entire location/cost table. In VBA, record range for entire travel table. Ask user for employee name. Make sure name is in first column of travel table. Highlight table row for given name. Lookup current number of nights from table for given name. Tell user current value and ask for new number of nights. Make sure number is positive value, otherwise ask them to input again. Make sure nights is a numeric value, otherwise use error handling to return to input prompt. Lookup current location from travel table and note travel cost from location table. Calculate new travel cost with location cost and updated number of nights (for $100/night). Modify the employee row with new number of nights and updated cost. Format new values to be bold and larger font. Give user confirmation message. Make sure to clear previous formats at beginning of procedure. Scheduled Travel EmployeeClient LocationNumber of NightsTotal CostClient LocationTravel Cost F. GarciaArlington3$562.50Arlington$262.50 S. TiagoArlington3$562.50Atlanta$450.00 D. EdwardsBaltimore1$675.00Baltimore$375.00 J. JonesArlington2$462.50Charlotte$150.00 M. JaneDC4$700.00DC$300.00 Richmond$187.50 Update Travel