PAUSE & PRACTICE: EXCEL 8-2

In this project, you assign a macro to a button, insert an ActiveX control, and copy code for an ActiveX control.

File Needed: [your initials] PP E8-1.xlsm

Completed Project File Name: [your initials] PP E8-2.xlsm

1. Display the Developer tab if it is not shown.
2. Open the Trust Center and set macro security to Disable VBA macros with notification.
3. Open the [your initials] PP E8-1.xlsm workbook completed in Pause & Practice 8-1.
4. Click Enable Content. (The security message may not open if you completed Pause & Practice 8-1 during the same work session.)
5. Save the workbook as a macro-enabled workbook named [your initials] PP E8-2 in your folder for saving files.
6. Insert a Button form control.
   1. Click the Insert Controls button [Developer tab, Controls group].
   2. Choose Button (Form Control) and click cell F4 to display the Assign Macro dialog box.
   3. Select Welcome in the list of macro names and click OK.
7. Format a Button control.
   1. Click the button control if the selection handles display. (If the button does not display selection handles, right-click to select it.)
   2. Click the Shape Format command tab on the Ribbon.
   3. Set the height to 0.5″ and the width to 2″.
   4. Right-click the control and select Edit Text.
   5. Change the caption to Display message.
   6. Click cell F8 to deselect the control.
8. Edit a macro in the Visual Basic Editor.
   1. Click the Macros button [Developer tab, Code group].
   2. Select Welcome in the list of macro names.
   3. Click Edit to open the VBE window. (If the Code window is not visible, choose Code from the View menu. Your macro may be in Module 2 or Module 3 in the Project Explorer window, double-click a module name to see its contents.)
   4. Select and delete “PVR” in the code and type Perfect as the department name (Figure 8-20).
   5. Locate and click the View Microsoft Excel button at the left side of the toolbar.
9. Run an edited macro from a Button form control.
   1. Select cell F2. You executed your macro earlier in Pause & Practice 8-1 and the message displays here.
   2. Click the Clear button [Home tab, Editing group] and select Clear All.
   3. Click the Display message button control. The new message is inserted in cell F2.
10. Run code from an ActiveX command button control.
    1. Unhide the ActiveX worksheet. It includes an ActiveX control.
    2. Click the CommandButton1 control. The data sorts in ascending order by rental name.
    3. Click the Design Mode button [Developer tab, Controls group].
    4. Right-click the control and select View Code (Figure 8-21).
    5. Press Alt+F11 to close the Visual Basic Editor.
11. Insert a Command Button control.
    1. Click the Insert Controls button [Developer tab, Controls group].
    2. Select the Command Button (ActiveX Control) command in the ActiveX Controls group.
    3. Click cell F8 to insert the control at a default size.
    4. Click the Properties button [Developer tab, Controls group].
    5. Select the Alphabetic tab.
    6. Click the BackColor drop-down list and select the Palette tab.
    7. Click the gray color icon in the first column, second row.
    8. Click the Caption line and replace the default label with Last Name.
    9. Click the Shadow drop-down list and choose True (Figure 8-22).
    10. Close the Properties window.
12. Size and align controls.
    1. Click the Last Name command button.
    2. Press Ctrl and click the CommandButton1 control so that both controls are selected.
    3. Click the Shape Format command tab on the Ribbon.
    4. Set the height at 0.5″ and the width at 1.5″ [Shape Format tab, Size group].
    5. Click the Align button [Shape Format tab, Arrange group] and choose Align Left.
    6. Click a worksheet cell to deselect the controls.
    7. Right-click the CommandButton1 button and choose Properties.
    8. Select the Alphabetic tab, click the BackColor drop-down list, and select the Palette tab.
    9. Click the gray color icon in the first column, second row.
    10. Click the Caption line and replace the default label with Rental.
    11. Click the Shadow drop-down list and choose True.
    12. Close the Properties window.
13. Write and copy code for an ActiveX command button.
    1. Verify that Design Mode is active [Developer tab, Controls group].
    2. Select the Last Name control.
    3. Click the View Code button [Developer tab, Controls group]. Your Visual Basic Editor should display the code for the CommandButton1 control.
    4. Click the Object arrow at the top of the Code window and select CommandButton2 (Figure 8-23). A new sub procedure area opens.
    5. Verify or click between the Private Sub… line and the End Sub line in the code for CommandButton2.
    6. Type an apostrophe (’) and press the Spacebar.
    7. Type Sort data by last name and press Enter. Your entry is evaluated, recognized as a comment, and displays in green.
    8. Select the code lines for the CommandButton1 control starting at the word “Range” and ending at “Range(“A5”).Select” and press Ctrl+C to copy it (Figure 8-24).
    9. Click above End Sub in the code area for CommandButton2 and paste the code.
14. Edit copied code for an ActiveX command button.
    1. Locate the line in the CommandButton2 procedure that refers to the: Key:…” This identifies the column used for sorting, originally column D, the rental name.
    2. Replace D5:D27 with A5:A27 in the copied code. The client’s last name is in column A.
    3. Click to the left of “Range (“A5:D27”)” in the first code line to position the insertion point.
    4. Press Enter and then press Tab to align the copied code. Positioning commands in the Code window are not entered as code.
    5. Close the Visual Basic Editor.
    6. Select cell F1 to deselect the control.
    7. Click the Design Mode button [Developer tab, Controls group] to turn off Design Mode.
    8. Click the Last Name command button to sort the data by name.
    9. Test the Rental button.
15. Save and close the workbook (Figure 8-25).
16. Hide the Developer tab if instructed to do so.