Ch 8 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]

File Needed: BoydLogistics-08.xlsx (Available from the Start File link.)

Completed Project File Name: [your name]-BoydLogistics-08.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.

Steps to complete This Project

Mark the steps as checked when you complete them.

  1. Open the BoydLogistics-08.xlsx start file. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
  2. 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.
  3. Save the workbook as a macro-enabled template.
    1. Choose Save As from the File tab.
    2. Choose Excel Macro-Enabled Template in the Save as type list. Note that the Custom Office Templates folder is selected [Figure 8-37].Figure 8-37 Macro-enabled template folder
    3. 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, 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).Figure 8-38 Paste values to replace TODAY
    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).Figure 8-39 Nested SORT and UNIQUE in macro
    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).Figure 8-40 Button controls aligned and sized
    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).Figure 8-41 Edited macro code
    5. Press Alt+F11 to return to the worksheet and click cell A3.
  11. Document the macros.
    1. Select the Documentation sheet tab.
    2. Select cell A3 and type Name Figure 8-42.Figure 8-42 Documentation for template
    3. Type Purpose in cell B3.
    4. Starting in cell A4, type DateStamp. Press Tab or right arrow to move to cell B4, and type Insert the current date as text. Press down arrow to move to cell B5 and type Assigned to the Stamp Date button.
    5. In cell A7, type SortUnique. Press Tab or right arrow to move to cell B7, and type Display the names of contractors. Press down arrow to move to cell B8 and type Assigned to the Contractors button.
    6. Apply All Borders to cells A3:B8.
    7. Size columns A:B so that all text is showing (Figure 8-42).
    8. Apply bold formatting to cells A3:B3 and apply Center alignment to the cells.
  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).Figure 8-43 Resized table
    5. Select cell A5.
    6. Click Save on the title bar to save your template.
  13. 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.)Figure 8-44 Data for workbook
    1. Type the record in row 5 starting in cell A5: Type today’s date; Smith Green; Minneapolis; Omaha; 380; 90
    2. Type the record in row 6 starting in cell A6: Type tomorrow’s date; AAA Trucking; Chicago; Minneapolis; 410; 87
    3. Type the record in row 7 starting in cell A7: Type yesterday’s date; Miles to Go; St. Louis; Fargo; 795; 85
  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 starting in cell A8 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 downloaded start file as an Excel Workbook (*.xlsx) for grading in SIMnet. Navigate to where you would save your project files to save your completed file. Click Yes in the message box saying “The following features cannot be saved in macro-free workbooks: VB project”.
  16. Hide the Developer tab if instructed to do so.
  17. Save and close the workbook (Figure 8-45).Figure 8-45 Excel 8-1 completed
  18. Upload and save your Excel Workbook (*.xlsx) project file.
  19. Submit file for grading.

Do you need help with this assignment or any other? We got you! Place your order and leave the rest to our experts.

Place Your Order

Quality Guaranteed

Any Deadline

No Plagiarism