Excel 365 In Practice - Ch 8 Guided Project 8-1
SIMnet Excel project instructions
· Excel Projects Help
Guided Project 8-1 - SIMnet Project 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.
3.Click the Options command [File tab] and click Trust Center in the left pane.
4.Click the Trust Center Settings button and click Macro Settings in the left pane.
5.Verify or select Disable VBA macros with notification. (Your dialog box may display slightly different wording.)
6.Click ActiveX Settings, choose Prompt me before enabling all controls with minimal restrictions, and click OK.
7.Click Customize Ribbon in the left pane, display the Developer tab, and close Excel Options.
8.Save the workbook as a macro-enabled template.
9.Choose Save As from the File tab.
10.Choose Excel Macro-Enabled Template in the Save as type list. Note that the Custom Office Templates folder is selected (Figure 8-42).
Macro-enabled template folder
Figure 8-42 Macro-enabled template folder
11.Click Save.
Record a macro with relative references to date stamp the sheet.
12.Click the Use Relative References button [Developer tab, Code group] to enable the command.
13.Select cell H1.
14.Click the Record Macro button [Developer tab, Code group].
15.Type DateStamp in the Macro name box. You will not use a shortcut because you will assign the macro to a button.
16.Verify or choose This Workbook in the Store macro in box.
17.Click the Description box, type Insert date, and click OK.
18.Type =tod, press Tab to select TODAY, and press Enter. This inserts =TODAY() in cell H1 and moves the insertion point to cell H2.
19.Press the up arrow one time and press Ctrl+C. This copies the TODAY function to the Clipboard. (A Create event button may display as shown in Figure 8-43.)
A screenshot shows two Excel sheets depicting paste options.The first screenshot shows the date 3 June 2026 at the top with a border. Below is a Create event drop-down menu with icons for Outlook logos. The text Contractors is given below. The second screenshot shows the date 3 June 2026 at the top with a border. The text Contractors is given below. A box titled Paste shows various paste options. The browser is shown on Values (V) under the Paste Values option. The dates with borders in both screenshots are jointly labeled Press E s c to remove button and border.
20.Press Esc to remove button and border
Figure 8-43 Paste values to replace TODAY
21.Press Ctrl+V to display the Paste Options button. (Press Esc if the Create event button obscures the area.)
22.Click the Paste Options button and choose Paste Values. This pastes the actual date and replaces =TODAY() (Figure 8-43).
23.Press Esc to remove the moving border.
24.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.
25.Click the Use Relative References button [Developer tab, Code group] to disable the command.
26.Close the Record Actions pane if it opens.
Test the macro.
27.Delete the contents of cell H1 and select cell H2.
28.Click the Macros button [Developer tab, Code group].
29.Select DateStamp and click Run. Today’s date is entered in cell H2 because the macro uses relative references.
30.Delete the contents of cell H2.
Record a macro with absolute references to display unique contractor names.
31.Confirm that the Use Relative References button [Developer tab, Code group] is not active.
32.Click the Record Macro button [Developer tab, Code group].
33.Type SortUnique in the Macro name box. You will use SORT and UNIQUE from the Lookup & Reference category.
Do not use a shortcut key.
34.Verify or choose This Workbook in the Store macro in box.
35.Click the Description box, type Display contractors for this period, and click OK.
36.Select cell I5, the first step in your macro.
37.Click the Lookup & Reference button [Formulas tab, Function Library group] and select SORT. The Array argument is a nested UNIQUE function.
38.Click the Name Box arrow, choose More Functions, and find and select UNIQUE in the Lookup & Reference category.
39.Confirm that the Function Arguments dialog box is for UNIQUE and select cells B5:B32. The structured reference name Table1[Contractor] is substituted.
40.Click SORT in the Formula bar to return to its Function Arguments dialog box (Figure 8-44).
A screenshot shows a Function Arguments dialog box.The screenshot shows the following formula at the top: equals SORT(UNIQUE(Table 1[Contractor]). A dialog box titled Function Arguments is shown below. The SORT section shows the following fields: Array: (UNIQUE(Table 1[Contractor]) equals {"Smith Green";"A A A Trucking"; "Mile. Sort_index: Blank equals array Sort_order: Blank equals array By_col: Blank equals logical Blank equals {"A A A Trucking";"Miles to Go";"Nee... Sorts a range or array. Array the range or array to sort. Formula result equals A A A Trucking. A hyperlink for Help on this function is shown on the bottom left. OK and Cancel buttons are shown on the bottom right. At the top right corner, a help icon and a close button are shown.
Figure 8-44 Nested SORT and UNIQUE in macro
41.Click OK.
42.Click the Stop Recording button to the right of the Ready indicator in the Status bar.
Test the macro.
43.Select cell I5 and press Delete. SORT and UNIQUE are dynamic array formulas, and the function is in the first cell of the array.
44.Select cell A3.
45.Click the Macros button [Developer tab, Code group], select SortUnique, and click Run. The macro runs in cell I5 as recorded.
46.Delete cell I5.
Assign macros to Button form controls.
47.Click the Insert Controls button [Developer tab, Controls group].
48.Click the Button (Form Control) command in the Form Controls category and click cell J2.
49.Select DateStamp in the Assign Macro dialog box and click OK.
50.Right-click the Button control and choose Edit Text.
51.Delete the default text and type Stamp Date as the label.
52.Right-click the control and choose Exit Edit Text. The label does not fit in the control.
53.Click the Insert Controls button [Developer tab, Controls group], select the Button (Form Control) command, and click cell J5.
Assign the SortUnique macro and edit the caption to Contractors.
Size and align Button form controls.
54.Right-click the Stamp Date button.
55.Press Ctrl and right-click the Contractors control. Both controls are selected.
56.Click the Align button [Shape Format tab, Arrange group] and select Align Left.
57.Click the Height box [Shape Format tab, Size group] and type 0.45.
58.Set the Width box [Shape Format tab, Size group] to 1.25.
While both controls are selected, drag them so that the top-left corner of the Stamp Date control is in cell J2 (Figure 8-45).
Button controls aligned and sized
Figure 8-45 Button controls aligned and sized
59.Click cell H1 to deselect the controls.
60.Edit a VBA macro.
61.Right-click the Contractors button and click the Visual Basic button [Developer tab, Code group].
62.Display the Project Explorer window if necessary [View menu].
Expand the Modules folder.
63.Display the Code window [View menu], if necessary. Your macros should be in Module1.
64.Find and double-click Module1.
65.Edit the code line in the SortUnique macro to select cell H5 instead of I5 (Figure 8-46).
A screenshot of an Excel sheet shows a table V B A Project box.The screenshot shows an Excel sheet titled Microsoft Visual Basic for Applications- Excel 8-1 Template dot x l s m. Buttons for minimize, maximize, and close are given on the top right. The Excel sheet Ribbon has tabs for File, Home, Insert, Draw, Page Layout, Formulas, Data, Review, View, and Help. A pane shows icons below. A box titled Project-V B A Project is shown on the left. The folder section is selected. The following list is given below: Solver (SOLVER DOT X L A M) V B A Project (Excel 8-1 Template dot x l t m) Microsoft Excel Objects Sheet 1 (Stats) Sheet 2 (Documentation) This Workbook Modules Module1 V B A Project (FUNCRES DOT X L A M) The box on the right is titled Excel 8-1 Template dot x l s m-Module 1 (Code). Drop-down menus for (General) and Sort Unique are shown below. The cursor is shown on (General) with the word Object. The main section shows the following code: Sub Date Stamp () Date Stamp Macro Insert date Active Cell dot Formula R 1 C 1 equals "TODAY ()" Active Cell dot Select Selection dot Copy Selection dot Paste Special Paste: equals x l Paste Values, Operation: equals x l None, Skip Blanks : equals False, Transpose: equals False Application dot Cut Copy Mode equals False End Sub Sub Sort Unique () 1 Sort Unique Macro Display contractor names for this period Range("H 5") Select Application. Cut Copy Mode equals False Active Cell. Formula 2 R 1 C 1 equals "SORT (UNIQUE (Table 1 [Contractor]))" End Sub H 5 is encircled. Buttons for minimize, maximize, and close are shown at the top right.
Figure 8-46 Edited macro code
66.Press Alt+F11 to return to the worksheet and click cell A3.
Document the macros.
67.Select the Documentation sheet tab.
68.Select cell A3 and type Name.
69.Type Purpose in cell B3.
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. Figure 8-47.
Documentation for template
Figure 8-47 Documentation for template
70.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.
71.Apply All Borders to cells A3:B8.
Size columns A:B so that all text is showing (Figure 8-47).
72.Apply bold formatting to cells A3:B3 and apply Center alignment to the cells.
73.Delete sample data and save the template.
74.Select the Stats sheet tab.
75.Select and delete cells A5:F32.
76.Click a table cell, 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.
77.Select cells A8:F32 and clear the formats (Figure 8-48).
Resized table
Figure 8-48 Resized table
78.Select cell A5.
79.Click Save in the Quick Access toolbar to save your template. The alert message about the Document Inspector pertains to the macros.
80.Enter three records as shown in Figure 8-49. 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.)
Data for workbook
Figure 8-49 Data for workbook
81.Type today's date Smith Green Minneapolis Omaha 380 90
82.Type tomorrow's date AAA Trucking Chicago Minneapolis 410 87
83.Type yesterday's date Miles to Go St. Louis Fargo 795 85
Run macros in the template.
84.Select cell H2 and click the Stamp Date button control. Your date is volatile.
85.Click the Contractors button control.
86.Select cell F7 and press Tab. A new row displays and is shown in the contractor’s list as 0.
87.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.
88.Type today's date
Neena Brothers
Milwaukee
Kansas City
565
69
89.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 (or Save) in the message box saying "The following features cannot be saved in macro-free workbooks: VB project" if it displays.
Hide the Developer tab if instructed to do so.
90.Save and close the workbook (Figure 8-50).
Excel 8-1 completed
Figure 8-50 Excel 8-1 completed
91.Upload and save your Excel Workbook (*.xlsx) project file.
92.Submit file for grading.