Guided Project 8-3

Clemenson Imaging has begun a macros-only workbook for use in developing weekly and monthly reports. You write new macros, copy and edit a macro, and prepare documentation.

[Student Learning Outcomes 8.1, 8.2, 8.3, 8.5, 8.8]

File Needed: Clemenson-08Macros.xlsm (Student data files are available in the Library of your SIMnet account.)

Completed Project File Names: [your initials] Excel 8-3Macros.xlsm and [your initials] Excel 8-3.xlsx

Skills Covered in This Project

• Set macro security options.
• Edit and save a macros-only workbook.
• Record a VBA macro.
• Copy code in the Visual Basic Editor.
• Record a macro with relative references.
• Run macros in a new workbook.

1. Set macro security and display the Developer tab.
   1. Select Options (File tab) and then click Trust Center.
   2. Click the Trust Center Settings button and click Macro Settings in the left pane.
   3. Verify or select Disable VBA macros with notification and click OK.
   4. Click Customize Ribbon in the left pane and display the Developer tab.
2. Open the Clemenson-08Macros workbook from your student data files and click Enable Content if the message box displays.
3. Save the workbook as an Excel macro-enabled workbook.
   1. Choose Save As from the File tab.
   2. Type [your initials] Excel 8-3Macros for the file name.
   3. Verify or choose Excel Macro-Enabled Workbook from the Save as type list.
   4. Click Save. You may see a reminder that the workbook has information that the Document Inspector cannot remove.
   5. Click OK if the message box displays.
4. Review and test macros.
   1. Review the documentation about the existing macros.
   2. Insert a new sheet and name it Test Sheet.
   3. Select cell A2 and press Ctrl+Shift+L to insert the label. The macro includes label formatting.
   4. Select cell A3 and Ctrl+Shift+W. This is the label for a weekly report.
5. Record a VBA macro.
   1. Select cell A6 on the Test Sheet. You will start the macro for the monthly report label.
   2. Click the Record Macro button [Developer tab, Code group].
   3. Type Monthly in the Macro name box.
   4. Press Shift and type M for the shortcut key.
   5. Store the macro in This Workbook.
   6. Type Display monthly label in the Description box and click OK.
   7. Click the Stop Recording button [Developer tab, Code group].
6. Edit and copy code in a VBA macro.
   1. Open the Visual Basic Editor and display the Code window and the Project Explorer [View menu].
   2. Expand the Modules group in the Project Explorer, double-click Module1, and then double-click Module2 to display the Code windows for both modules.
   3. Locate the Sub Weekly() macro code.
   4. Select the code starting at Selection.FormulaR1C1 through End With (Figure 8-53).
   5. Press Ctrl+C to copy the code to the Clipboard. You can copy code from one module to another when necessary.
   6. Locate the Sub Monthly() code, click after the apostrophe for the blank line above End Sub, and press Enter.
   7. Press Ctrl+V to paste the code.
   8. Edit the first code line to display Monthly Report (Figure 8-54).
7. Insert a comment in VBA code.
   1. Scroll or switch Code window to display the Sub Weekly() macro.
   2. Click the empty green comment line below Weekly Macro and press the Spacebar.
   3. Type Display weekly label and press Enter.
   4. Close the Visual Basic Editor.
   5. Select and delete cell A3.
   6. Press Ctrl+Shift+M. (If your macro does not execute, delete it [Macro dialog box] and repeat steps 5-6.)
8. Complete documentation for the Monthly macro.
   1. Click the Macros sheet tab.
   2. Copy cells B8:B10 to cells B12:B14.
   3. Edit the text in cell B12 to show Monthly in place of “Weekly.”
   4. Edit the shortcut in cell B14 to show M in place of “W.”
9. Record a VBA macro with relative references to complete the body of the form.
   1. Click the Test Sheet tab.
   2. Click the Use Relative References button [Developer tab, Code group] to activate the command.
   3. Select cell A8.
   4. Click the Record Macro button [Developer tab, Code group] and type Body in the Macro name box.
   5. Press Shift and type B for the shortcut key and store the macro in This Workbook.
   6. Type Build form in the Description box and click OK.
   7. Type the four labels shown here, pressing Tab to move from one column to the next, but press Home after “Image Type.” The labels display in cells A8:D8.TechnicianLocationDate Image Type 
   8. Confirm that the insertion point is in cell A8, press Shift, and press right arrow three times to select cells A8:D8.
   9. Apply bold and center alignment.
   10. Click the Format button [Home tab, Cells group] and set the column width at 18.
   11. Press Shift and down arrow to reach row 30.
   12. Apply All Borders and press Home.
   13. Click the Stop Recording button [Developer tab, Code group] (Figure 8-55).
   14. Turn off the Use Relative References button.
10. Test the macro and complete documentation.
    1. Select cell G8 and run the Body macro. The macro executes tasks relative to the starting cell.
    2. Click the Macros sheet tab and select cell B16.
    3. Complete documentation as shown in Figure 8-56.
    4. Save your macros-only workbook and leave it open.
11. Run macros in a new workbook.
    1. Open the Clemenson-08 workbook from your student data files and save it as [your initials] Excel 8-3 in your usual folder. This is a regular Excel workbook.
    2. Select cell A2 and press Ctrl+Shift+L to run the EnterLabel macro.
    3. Select cell A3 and click the Macros button [Developer tab, Code group]. The Macro dialog box displays macro names from all open workbooks.
    4. Select Excel 8-3Macros.xlsm!Weekly and click Run (Figure 8-57).
    5. Select cell A5 and run the Body macro.
12. Select cell A6 and enter data for three records. Use mm/dd/yy format for the dates. Your dates are volatile and will not match Figure 8-58.McFarlandAppletonYesterdayMRIVonbankGreen BayTodayCT ScanDouglasManitowocTomorrowAngiography
13. Save and close your workbook (Figure 8-58).
14. Save and close your macros workbook. Click OK if the message box about personal information displays.
15. Hide the Developer tab if instructed to do so.