Guided Project 8-2

In the Eller Systems workbook, you add a record using the built-in UserForm. You record macros to filter and extract data and to clear filter results.

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

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

Completed Project File Name: [your initials] Excel 8-2.xlsm

Skills Covered in This Project

  • Set macro security options.
  • Display the Form button.
  • Enter data in a built-in UserForm.
  • Record VBA macros.
  • Run macros.
  • Assign macros to Button form controls.
  • Review and print VBA macro code.
  1. Set macro security and display the Developer tab and the Form button.
    1. Select the Options command [File tab] and click Trust Center in the left pane.
    2. Click the Trust Center Settings button and choose 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.
    5. Click Quick Access Toolbar and confirm or select the Show Quick Access Toolbar box.
    6. Select All Commands from the Choose commands from list.
    7. Find and select Form… in the list and click Add.
    8. Click OK.
  2. Open the Eller-08.xlsx workbook from your student data files.
  3. Save the workbook as an Excel macro-enabled workbook.
    1. Choose Save As from the File tab and navigate to your folder.
    2. Type [your initials] Excel 8-2 as the file name.
    3. Choose Excel Macro-Enabled Workbook from the Save as type list.
    4. Click Save.
  4. Add records using a data form.
    1. Select cell A4 and click the Format as Table button [Home tab, Styles group].
    2. Format the existing data using White, Table Style Light 8.
    3. Click the Name Box arrow and choose Table1. This is the default table name, and it does not include the header row.
    4. Click the Form button on the Quick Access toolbar. The input form displays data for the first record.
    5. Click New in the data form to open a new entry form.
    6. Type the data for a new record as shown in Figure 8-46.
    7. Click Close to close the data form.
    8. Scroll as needed to see the new record.
    9. Click the Name Box arrow and choose Table1. The new row is included in the table.
    10. Click the Save button on the title bar to save the workbook with the new record.
  5. Record a macro to filter clients.
    1. Select cell A4.
    2. Verify that the Use Relative References button [Developer tab, Code group] is off.
    3. Click the Record Macro button [Developer tab, Code group].
    4. Type FilterData in the Macro name box.
    5. Do not use a shortcut key and store the macro in This Workbook.
    6. Click the Description box, type Filter for selected service, and click OK. All commands and selections to build an advanced filter are recorded.
    7. Click the Advanced button [Data tab, Sort & Filter group] and select the Copy to another location radio button.
    8. Click the Up arrow at the right of the List Range box. The arrow collapses the dialog box.
    9. Select cells A4:F52 and click the Down arrow to expand the dialog box. The range name Hours is substituted and includes the header row.
    10. Click the Criteria range box and select cell J8:J9.
    11. Click the Copy to box and select cells J12:L12 (Figure 8-47).
    12. Click OK to run the filter.
    13. Click the Stop Recording button to the right of the Ready indicator in the Status bar.
  6. Record a macro to clear filter results.
    1. Click the Record Macro button [Developer tab, Code group].
    2. Type ClearResults in the Macro name box.
    3. Do not use a shortcut key and store the macro in This Workbook.
    4. Click the Description box, type Clear filter, and click OK.
    5. Select cell J13, the first cell that displays filter results.
    6. Press F8 (FN+F8) to start Extend Selection mode.
    7. Press the right arrow two times.
    8. Press Ctrl and then press the down arrow once to highlight to the end of the filter results.
    9. Click the arrow on the Clear button [Home tab, Editing group] and select Clear All.
    10. Select cell A4.
    11. Click the Stop Recording button to the right of the Ready indicator in the Status bar.
  7. Assign macros to Button form controls.
    1. Click the Insert Controls button [Developer tab, Controls group] and choose the Button (Form Control) command in the Form Controls category.
    2. Click cell H4, assign the FilterData macro, click OK.
    3. Right-click the button and choose Edit Text.
    4. Delete the default caption and type Show Results as the label.
    5. Select cell I4 to deselect the control.
    6. Insert another Button form control in cell H7 and assign the ClearResults macro.
    7. Edit the caption to display Clear Results.
  8. Size and align controls.
    1. Right-click the Show Results button to select it.
    2. Press Ctrl and right-click the Clear Results button. Both buttons display selection handles.
    3. Click the Height box [Shape Format tab, Size group] and type 0.35 and press Enter. The controls are the same height.
    4. Click the Width box [Shape Format tab, Size group], type 1, and press Enter.
    5. Click the Align button [Shape Format tab, Arrange group] and select Align Left (Figure 8-48).
    6. Select cell A4.
  9. Run the macros.
    1. Click the combo box arrow and select Web Site Development. The combo box uses cell J5 as its linked cell. Cell J9 has an INDEX formula that displays the data in the linked cell (J5) which is hidden beneath the combo box.
    2. Click the Show Results button to run the macro.
    3. Add a bottom border to row 25 in the results and a right border to the Hours column (Figure 8-49).
  10. Review, edit, and print macro code in the Visual Basic Editor.
    1. Click the Macros button [Developer tab, Code group] and select Edit.
    2. Display the Code window if necessary [View menu]. The code for two macros should be contained in Module 1. (If your code is not in Module 1, complete the steps to print and then expand Module 2 to print the second macro.)
    3. Size the Code window to display your macros.
    4. Delete unnecessary code lines and comment lines. Depending on movement and other commands you may have included in your macro, you can remove lines as needed (Figure 8-50).
    5. After you edit the code, run the ClearResults macro again to test it.
    6. Select a different service and run the FilterData macro to test it.
    7. Return to the Visual Basic Editor and display your code.
    8. Click File in the menu and select Print.
    9. Verify or select the Current Module radio button.
    10. Verify or select the Code box (Figure 8-51).
    11. Click OK to send the document to the current printer or click Cancel if no printer is available.
    12. Click the Close button for the Visual Basic Editor and return to Excel.
  11. Run the cleaned macros.
    1. Run the ClearResults macro if necessary to remove data.
    2. Choose Accounting Installation in the combo box and click the Show Results button.
    3. Complete borders as needed (Figure 8-52).
    4. Save and close the workbook.
  12. Hide the Developer tab and remove the Form button from the Quick Access toolbar.
  13. Hide the Quick Access toolbar.

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