Guided Project 8-1

Guided Project 8-1

Boyd Logistics is developing a template with macros. To complete the template you record macros, assign them to Button form controls, edit VBA code, and create a macro-enabled workbook.

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

 DO NOT use the files below if you are completing an autograded SIMnet Project. Using the linked files below will prevent you from uploading and submitting your work in SIMnet. The files for all autograded SIMnet Projects MUST be downloaded from your class assignment.

Files Needed: BoydLogistics-08.xlsx (Student data files are available in the Library of your SIMnet account.)

Completed Project File Names: [your initials] Excel 8-1Template.xltm and [your initials] Excel 8-1.xlsx

Skills Covered in This Project

• Set macro security options.
• Save a macro-enabled template.
• Record a VBA macro.
• Record a macro with relative references.
• Record a macro with absolute references.
• Run VBA macros.
• Assign macros to Button form controls.
• Edit a macro in the Visual Basic Editor.

1. Set macro security and display the Developer tab.
   1. Click the Options command [File tab] and click Trust Center in the left pane.
   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. (Corporate and early Excel versions may display Disable all macros with notification.)
   4. Click Customize Ribbon in the left pane, display the Developer tab, and close Excel Options.
2. Open the BoydLogistics-08.xlsx workbook from your student data files.
3. Save the workbook as a macro-enabled template.
   1. Choose Save As from the File tab.
   2. Type [your initials] Excel 8-1Template as the file name.
   3. Choose Excel Macro-Enabled Template in the Save as type list. Note that the Custom Office Templates folder is selected [Figure 8-37].
   4. Click Save.
4. Record a macro with relative references to date stamp the sheet.
   1. Click the Use Relative References button [Developer tab, Code group] to enable the command.
   2. Select cell H1.
   3. Click the Record Macro button [Developer tab, Code group].
   4. Type DateStamp in the Macro name box. You will not use a shortcut because you will assign the macro to a button.
   5. Verify or choose This Workbook in the Store macro in box.
   6. Click the Description box, type Insert date text, and click OK.
   7. Type =to, press Tab to select TODAY, and press Enter. This inserts today’s date in cell H1 and moves the insertion point to cell H2.
   8. Press the up arrow one time and press Ctrl+C. This copies today’s date to the Clipboard.
   9. Press Ctrl+V to paste the date in cell H1. This displays the Paste Options button (Figure 8-38).
   10. Click the Paste Options button and choose Paste Values. This pastes the date as text and replaces =TODAY(). The date is no longer volatile.
   11. Press Esc to remove the moving border.
   12. Click the Stop Recording button to the right of the Ready indicator in the Status bar. The ScreenTip for the button is A macro is currently recording. Click to stop recording.
   13. Click the Use Relative References button [Developer tab, Code group] to disable the command.
5. Test the macro.
   1. Delete the contents of cell H1 and select cell H2.
   2. Click the Macros button [Developer tab, Code group].
   3. Select DateStamp and click Run. Today’s date is entered as text in cell H2.
   4. Delete the contents of cell H2.
6. Record a macro with absolute references to display unique contractor names.
   1. Confirm that the Use Relative References button [Developer tab, Code group] is not active.
   2. Click the Record Macro button [Developer tab, Code group].
   3. Type SortUnique in the Macro name box. You will use SORT and UNIQUE from the Lookup & Reference category.
   4. Do not use a shortcut key.
   5. Verify or choose This Workbook in the Store macro in box.
   6. Click the Description box, type Display contractors for this period, and click OK.
   7. Select cell I5. This is the first code in your macro—to select cell I5.
   8. Click the Lookup & Reference button [Formulas tab, Function Library group] and select SORT. The Array argument is a nested UNIQUE function.
   9. Click the Name Box arrow, choose More Functions, and find and select UNIQUE in the Lookup & Reference category.
   10. Confirm that the Function Arguments dialog box is for UNIQUE and select cells B5:B32. The structured reference name Table1[Contractor] is substituted.
   11. Click SORT in the Formula bar to return to its Function Arguments dialog box (Figure 8-39).
   12. Click OK.
   13. Click the Stop Recording button to the right of the Ready indicator in the Status bar.
7. Test the macro.
   1. Select cell I5 and press Delete. SORT and UNIQUE are dynamic array formulas, and the function is in the first cell of the array.
   2. Select cell A3.
   3. Click the Macros button [Developer tab, Code group], select SortUnique, and click Run. The macro runs in cell I5 as recorded.
   4. Select cell I5 and delete the contents.
8. Assign macros to Button form controls.
   1. Click the Insert Controls button [Developer tab, Controls group].
   2. Click the Button (Form Control) command in the Form Controls category and click cell J2.
   3. Select DateStamp in the Assign Macro dialog box and click OK.
   4. Right-click the Button control and choose Edit Text.
   5. Delete the default text and type Stamp Date as the label.
   6. Right-click the control and choose Exit Edit Text. The label does not fit in the control.
   7. Click the Insert Controls button [Developer tab, Controls group], select the Button (Form Control) command, and click cell J5.
   8. Assign the SortUnique macro and edit the caption to Contractors.
9. Size and align Button form controls.
   1. Right-click the Stamp Date button.
   2. Press Ctrl and right-click the Contractors control. Both controls are selected.
   3. Click the Align button [Shape Format tab, Arrange group] and select Align Left.
   4. Click the Height box [Shape Format tab, Size group] and type 0.45.
   5. Set the Width box [Shape Format tab, Size group] to 1.25.
   6. While both controls are selected, drag them so that the top-left corner of the Stamp Date control is in cell J2 (Figure 8-40).
   7. Click cell H1 to deselect the controls.
10. Edit a VBA macro.
    1. Right-click the Contractors button and click the Visual Basic button [Developer tab, Code group].
    2. Display the Project Explorer window if necessary [View menu].
    3. Display the Code window [View menu], if necessary. Your macro should be in Module1.
    4. Edit the code line in the SortUnique macro to select cell H5 instead of I5 (Figure 8-41).
    5. Press Alt+F11 to return to the worksheet and click cell A3.
11. Document the macros.
    1. Select the Documentation sheet tab.
    2. Type and format the information as shown in Figure 8-42.
12. Delete sample data and save the template.
    1. Select the Stats sheet tab.
    2. Select and delete cells A5:F32.
    3. Point to the table resize arrow in cell F32 and drag up to size the table to end at cell F7. This leaves three blank rows in the template but the table will expand as needed when data is entered.
    4. Select cells A8:F32 and clear the formats (Figure 8-43).
    5. Select cell A5.
    6. Click Save on the title bar to save your template.
    7. Close the template.
13. Create a workbook from the template.
    1. Click the File tab and select New.
    2. Click Personal as the category and select [your initials] Excel 8-1Template.
    3. Click Enable Content (or Enable Macros) if the security bar opens.
    4. Enter three records as shown in Figure 8-44. After the third record, press Home to return the insertion point to cell A7. If you press Enter after the third record, a blank row is added to the table. If this happens, click the Undo button to remove the blank row. (If you use the TODAY function in cell A5, the table automatically completes the column. Click the AutoCorrect Options button and undo the task.)
14. Run macros in the template.
    1. Select cell H2 and click the Stamp Date button control.
    2. Select cell H5 and click the Contractors button control.
    3. Select cell F7 and press Tab. A new row displays and calculates in the contractor’s list as 0.
    4. Enter the record shown here and press Home after the last value. Note that the Contractors list in column H updates as you complete the entry.The record you need to enter starting in cell A8 on the Stats worksheet.Type today’s dateNeena BrothersMilwaukeeKansas City56569
15. Select cell A5 and save your file as a macro-enabled workbook named [your initials] Excel 8-1 in your usual folder for saving files.
16. Close the workbook (Figure 8-45).
17. Move [your initials] Excel 8-1Template from the Custom Office Templates folder to your usual folder.
18. Hide the Developer tab if instructed to do so.