Independent Project 8-5

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]

 DO NOT use the files below if you are completing an autograded SIMnet Project. Using the linked files below will prevent you from uploading and submitting your work in SIMnet. The files for all autograded SIMnet Projects MUST be downloaded from your class assignment.

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

Completed Project File Names: [your initials] Excel 8-5Macros.xlsm and [your initials] Excel 8-5.xlsx

Skills Covered in This Project

• Set macro security options.
• Create a macros-only workbook.
• 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.

1. Set macro security to Disable VBA macros with notification and display the Developer tab.
2. Create a new workbook and save it as an Excel Macro-Enabled Workbook named [your initials] Excel 8-5Macros in your usual location for saving files.
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.Barre + 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.
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).
   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.
8. Open the CourtyardMedical-08 workbook from your student data files and save it as an Excel workbook named [your initials] Excel 8-5 in your folder.
9. 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.)
   12. Click the View Microsoft Excel button to return to the worksheet.
10. 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.
11. 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.
12. Resave the workbook.
    1. Click the Save button on the title bar to resave the workbook (Figure 8-68). Because you have added code for the ActiveX control, the workbook must be saved as a macro-enabled workbook.
    2. Click No in the message box.
    3. Navigate to your folder in the Save As dialog box and select [your initials] Excel 8-5. This is currently a regular Excel workbook.
    4. Click the Save as type drop-down list and choose Excel Macro-Enabled Workbook.
    5. Click Save. You now have two files named [your initials] Excel 8-5, one with the xlsm file name extension and the other with xlsx.
13. 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 initials] 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.
    6. Resave the macro-enabled workbook and close it.
    7. Close your macros-only workbook.
14. Hide the Developer tab if instructed to do so. (Figure 8-69).