Ch 8 Independent Project 8-5 (Windows Only)

Independent Project 8-5

These instructions are only compatible with the Microsoft Windows operating system.

Courtyard Medical Plaza developed a trainer’s worksheet for personalized workout plans. You record and edit macros for a macros-only workbook and complete an ActiveX control in the trainer’s workbook.

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

File Needed: CourtyardMedical-08.xlsx (Available from the Start File link.) and Excel 8-5Macros.xlsm (Available from the Resources link.)

Completed Project File Name: [your name]-CourtyardMedical-08.xlsx

Skills Covered in This Project

• Set macro security options.
• Record a macro with relative references.
• Run macros with keyboard shortcuts.
• Edit code in the Visual Basic Editor.
• Set properties for an ActiveX control.
• Use a VBA function in an ActiveX control.

Steps to complete This Project

Mark the steps as checked when you complete them.

1. Open the CourtyardMedical-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. Open the Excel 8-5Macros.xlsm file downloaded from the Resources link. This file is an Excel Macro-Enabled Workbook. Set macro security to Disable VBA macros with notification and display the Developer tab. IMPORTANT NOTE: You will be working in the Excel 8-5Macros.xlsm file for steps 3 through 6.
3. Record a macro with relative references. You should be working in the Excel 8-5Macros.xlsm file.
   1. Activate the Use Relative References command and select cell A1.
   2. Record a macro named Titles with a shortcut of Ctrl+Shift+T in This Workbook.
   3. Type Column titles for the Description and click OK. You are now recording steps in your macro.
   4. Type Activities and press the right arrow key.
   5. Type Calories and press the right arrow key.
   6. Type Times and press the right arrow key.
   7. Type Burned and press the left arrow key three times to return to the “Activities” label.
   8. Select cells A1:D1 and apply bold and italic.
   9. Select cell A1 and stop recording.
4. Record a macro with relative references.
   1. Select cell A4 and record a macro with relative references named Activities with a shortcut of Ctrl+Shift+A in This Workbook. Type Activity names for the Description.
   2. Type the labels shown in Figure 8-64 and press Enter after each one to move from row to row. Backspace to correct a typing error before you press Enter. Errors that you notice after you press Enter can be corrected later in the code window.Figure 8-64 Macro data entryBarre + CardioHip HopLatin FusionRunningSwimmingZumba
   3. Stop recording.
   4. Turn off Use Relative References.
5. Document the macros.
   1. Select cell F1 and type Titles Macro.
   2. Remove bold and italic from cell F1.
   3. Select cell F2 and type Ctrl+Shift+T.
   4. Select cell F4 and complete the documentation as shown in Figure 8-65. In cell F4, type Activities Macro. In cell F5, type Ctrl+Shift+A.Figure 8-65 Documentation for macros
6. Edit and print the VBA macro code.
   1. Edit the Titles macro in the Visual Basic Editor to change the word “Activities” in the first line of code to Activity.
   2. Review your macros and correct typing errors as needed.
   3. Size the Code window in the Visual Basic Editor so that you can see both macros. Print the code [File menu] if instructed to do so (Figure 8-66).igure 8-66 Print code from the File menu
   4. Close the Visual Basic Editor to return to the worksheet.
   5. Edit cell A1 to display Activity.
7. Save the macros-only workbook and leave it open. Switch to the CourtyardMedical-08 start file. IMPORTANT NOTE: From this point forward you will be working in the CourtyardMedical-08 start file.
8. Write code for an ActiveX command button on the worksheet.
   1. Click the Design Mode button [Developer tab, Controls group].
   2. Right-click the CommandButton1 control and select View Code.
   3. Click between Private Sub and End Sub in the Code window if necessary.
   4. Type msgbox and press the Spacebar. The first argument is the Prompt.
   5. Type “Welcome to CMP” and press the Spacebar after the closing quotation mark.
   6. Type &vbcrlf to include a Visual Basic carrier return/line feed.
   7. Press the Spacebar, type &, and press the Spacebar again. The ampersand concatenates each part of the prompt.
   8. Type “Click OK to complete the worksheet”.
   9. Type , (a comma), to move to the next argument Buttons. You will skip this argument so that the message box displays an OK button.
   10. Type , (a comma), to move to the Title argument.
   11. Type “Courtyard Medical Plaza” and press Enter (Figure 8-67). (If your code is not recognized, delete your lines and start at Step 9c again.)Figure 8-67 MsgBox code for ActiveX control
   12. Click the View Microsoft Excel button to return to the worksheet.
9. Test the ActiveX code.
   1. Click the Design Mode button [Developer tab, Controls group] to deactivate the mode.
   2. Click the CommandButton1 control. This UserForm is modal which means you cannot proceed until you respond.
   3. Click OK in the message box.
10. Set properties for the ActiveX control.
    1. Activate Design Mode and select the control.
    2. Open the Properties window.
    3. Change the caption to Please click here.
    4. Change the BackColor to the light red color (second column, first row) in the Palette.
    5. Turn on the Shadow and set the Width to 100. You may see the width adjust to a decimal value close to 100.
    6. Close the Properties window and turn off Design Mode.
    7. Select cell A3.
11. Save the CourtyardMedical-08 workbook. Click Yes in the message box saying “The following features cannot be saved in macro-free workbooks: VB project” (Figure 8-68).Figure 8-68 Save message box
12. Prepare the final workbook.
    1. Click the Please click here button and click OK in the message box.
    2. Verify that cell A3 is active and press Ctrl+Shift+T to run the Titles macro. The macros are stored in your Resource file, Excel 8-5Macros, which is still open.
    3. Run the Titles macro starting in each of cells F3, A15, and F15.
    4. Select cell A4 and press Ctrl+Shift+A.
    5. Run the Activities macro starting in each of cells F4, A16, and F16.
13. Save and close the macros-only workbook (Excel 8-5Macros.xlsm).
14. Hide the Developer tab if instructed to do so. (Figure 8-69).Figure 8-69 Excel 8-5 completed
15. Upload and save your Excel Workbook (*.xlsx) project file.
16. Submit file for grading.