Independent Project 8-4

Independent Project 8-4

At WearEver Accessories, you use a macro to identify products for reorder. You plan to place the macro code in an ActiveX control so that you can match the design to an existing button. You also record a macro to clear conditional formatting.

[Student Learning Outcomes 8.1, 8.2, 8.5, 8.6, 8.7]

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

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

Skills Covered in This Project

• Set macro security options.
• Run and review a VBA macro.
• Edit macro code in the Visual Basic Editor.
• Insert an ActiveX control.
• Copy VBA code for an ActiveX control.
• Display and edit a UserForm.

1. Set macro security to Disable VBA macros with notification and display the Developer tab.
2. Open the WearEver-08 workbook from your student data files and enable macros.
3. Save the workbook as an Excel Macro-Enabled Workbook named [your initials] Excel 8-4 in your usual location for saving files. Accept the Document Inspector alert.
4. Click the Trace Error button for cell A2. It is described as Misleading format. The cell refers to cell H1 which will display as you complete the workbook.
5. Run the Reorder macro. It applies conditional formatting to cells in the Quantity column with a value less than 4 but misses several rows. The macro should reach to row 50.
6. Clear conditional formatting from the sheet.
7. Edit a VBA macro.
   1. Click the Macros button [Developer tab, Code group] and edit the Reorder macro.
   2. Change the first line of code to display “E4:E50” as the range. This code indicates that a cell range is active or selected.
   3. Click after the word False at the end of the code and press Enter to insert a blank line.
   4. Point in the left margin area next to the line that displays Range(“E4:E50”). Select to display a white arrow pointer and click to select the line.
   5. Press Ctrl+C to copy the line.
   6. Click the empty line that you inserted in Step 7c and press Ctrl+V to copy the code.
   7. Edit the copied line to display Range(“A2”).Select. This returns the insertion point to cell A2 after conditional formatting is applied (Figure 8-59).
   8. Close the Visual Basic Editor.
   9. Run the Reorder macro to check your editing.
   10. Clear conditional formatting from the sheet.
8. Insert an ActiveX command button control.
   1. Insert a Command Button (ActiveX Control) at I6.
   2. Set the BackColor property to the green color tile in the fifth column, first row in the Palette.
   3. Change the Caption to Reorder.
   4. Set the Shadow property to True.
   5. Align the controls on the left and set their widths 1.2″.
9. Copy macro code in the Visual Basic Editor.
   1. Confirm that Design Mode is active and select the Reorder control if necessary.
   2. Click the View Code button [Developer tab, Controls group]. Verify that you see the code for Private Sub CommandButton1_Click(). The drop-down list at the top right of the Code window confirms the selected control.
   3. Choose CommandButton2 from the objects drop-down list. No code exists yet for this button.
   4. Display the Project Explorer window if necessary and double-click Module1 to display the Reorder macro code.
   5. Arrange the code windows as shown in Figure 8-60. (Cascade the code windows [Windows menu] if necessary. Your Explorer and Properties windows may display differently.)
   6. Select the code in the macro window from Range (“E4:E50”). Select through Range (“A2”). Select at the end of the code.
   7. Copy the code.
   8. Click the blank line between Private Sub and End Sub in the CommandButton2 window and paste the code.
   9. Click in front of Range (“E4:E50”) in the pasted code. Press Enter, and press Tab. These are cosmetic commands and do not affect the code (Figure 8-61).
   10. Close the Visual Basic Editor and select cell A2.
10. Record a macro to clear conditional formatting.
    1. Record a macro named Clear with a shortcut of Ctrl+Shift+C in This Workbook.
    2. Type Clear formatting as the Description and click OK.
    3. Click the Conditional Formatting button [Home tab, Styles group].
    4. Choose the command to clear conditional formatting from the sheet.
    5. Select cell A2 and stop recording.
11. Test the ActiveX control and the macro.
    1. Turn off Design Mode [Developer tab, Controls group].
    2. Click the Reorder control.
    3. Press Ctrl+Shift+C to clear the formatting.
12. Review and edit code for an ActiveX control.
    1. Click the Get Started control. An input box displays (Figure 8-62).
    2. Type today’s date in the format suggested in the input box and click OK. The date displays in cell H2, but the column is not wide enough.
    3. AutoFit column H.
    4. Delete the contents of cell H2.
    5. Turn on Design Mode [Developer tab, Controls group].
    6. Right click the Get Started control and select View Code. Verify that you see the code for CommandButton1_Click().
    7. Find the InputBox command. This is a VBA function, like the MsgBox, that results in a built-in UserForm.
    8. Find the line that specifies cell H2 as the destination for the “reply.”
    9. Change H2 to H1.
    10. Close the Visual Basic Editor.
13. Run code in ActiveX controls
    1. Turn off Design Mode and click the Get Started button.
    2. Enter the date and click OK.
    3. Click the Reorder button.
14. Hide the Developer tab if instructed to do so.
15. Save and close the workbook (Figure 8-63).