One of Kiola’s colleagues in HR uses an Excel worksheet to keep track of daily information regarding appointments and visitors expected at the company’s campus. Each morning this information is printed and distributed to each of the receptionists and administrative assistants. The receptionists are given one version and the administrative assistants another. Kiola’s colleague would like your help in creating a macro to print out the worksheet in the two required formats. To make it easy for anyone to produce the appointment and visitor lists, HR would like the capability of launching the macro from a shortcut key sequence, a macro button on a custom ribbon tab, and a custom button directly on the worksheet. 1. Open the workbook named DailyList.xlsx attached with the data of the assignment, and save the file as the macro-enabled workbook 10-5-DailyList-Monday-YourName.xlsx. If the DEVELOPER tab is not already visible, add it to the ribbon. TROUBLESHOOTING: In order to complete this step, the macro settings on your computer must be enabled. If you receive a Security Warning saying macros have been disabled or the file can’t be saved as a macro-enabled workbook, open the Trust Center to check the macro security settings. If you don’t have access to the Trust Center, contact your instructor or computer support person for assistance. 2. Enter your name and current data in the cells highlighted in yellow. 3. Plan the macro steps and then record the macro, naming it PrintText, and saving it in the current workbook. Assign the macro to the Ctrl+Shift+F shortcut key combination. Enter a description for the macro. Perform the following steps while recording the macro: For the first printout, specify: “¢ Custom header with Visitors – Admin List in the top center and page number on the top right “¢ Landscape orientation “¢ Print document to default printer (printer currently selected) For the second printout and to reset the worksheet, make the following modifications: “¢ Hide columns G and H “¢ Change the custom header to Visitors – Receptionist List “¢ Set the orientation to Portrait “¢ Print the document to the default printer “¢ Unhide columns G and H 4. Run the macro to test it. If it doesn’t perform all the tasks you planned, re-record the macro using the same name. (Hint: You must enter a new shortcut key because Ctrl+Shift+F has already been assigned to the first macro you recorded.) 5. Add a custom button on the Monday worksheet with the label PRINT VISITOR LISTS to run the macro. Place it at the top of the worksheet around columns F and G. 6. Add a macro button named Print Visitor List to a new ribbon tab named CUSTOM in a group called Macros. Place this new ribbon tab at the end of all existing tabs. Add the command buttons to Hide Columns and to Unhide Columns in this ribbon group. 2 WEEK # 7 – EXERCISES CHAPTER # 10 (Hint: Search for the commands Hide Columns and Unhide Columns from the All Commands list.) 7. Save the workbook, and then test the macro buttons. 8. View the code in the Visual Basic Editor. Copy the code starting from the beginning (Sub) until the first End With statement. (Hint: Select the code to highlight it and then use Ctrl+C to copy the code to the Clipboard.) Exit the VBA Editor, and then paste (press Ctrl+V) the code onto a new worksheet named code. 9. Save and close the 10-5-DailyList-Monday-YourName.xlsx workbook