Advanced Project 8-7

Advanced Project 8-7

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

In the Blue Lake Sporting Goods invoice template, you record a macro and add code for an ActiveX control. You then create a workbook from the template.

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

 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: BlueLake-08.xltx (Student data files are available in the Library of your SIMnet account.)

Completed Project File Names: [your initials] Excel 8-7Template.xltm and [your initials] Excel 8-7.xlsm

Skills Covered in This Project

• Set macro security options.
• Record and run a macro.
• Assign a macro to a form control.
• Add VBA code for an ActiveX control.
• Use a VBA function to display a built-in UserForm.
• Save a macro-enabled template.

1. Set macro security to Disable VBA macros with notification and display the Developer tab.
2. Open the BlueLake-08 template from your student data files.
3. Save the template as a macro-enabled template named [your initials] Excel 8-7Template in the Custom Office Templates folder.
4. Record a macro to insert the current date.
   1. Unprotect the sheet and remove workbook protection.
   2. Record a macro with absolute references named PODate. Do not use a shortcut or a description.
   3. Select cell D9, insert the TODAY function, and stop recording. Delete the contents of cell D9.
   4. Insert an Excel Button (Form Control) that covers cells G4 and assign the macro.
   5. Edit the text to Date.
   6. Format the control to use a bold font.
   7. Test the button and then delete the date in cell D9.
5. Insert an ActiveX control with a VBA function.
   1. Draw a Command Button (ActiveX Control) that covers cell G2.
   2. Change the caption to PO #.
   3. Edit the font to use bold.
   4. Enter code for the control as shown in Figure 8-73. Comment lines begin with an apostrophe and appear in green when you move to the next line. Comments are not part of the code and are not necessary for the button to function. Use them as an aid in expanding your Visual Basic for Applications skills.
   5. Test the button, type 1234, and click OK.
   6. Delete the contents of cell D7.
6. Size and align controls.
   1. Activate Design Mode and select both controls.
   2. Set a width of .75 and a height of .35 [Shape Format tab].
   3. Align the controls on the left.
7. Create a workbook from the template.
   1. Select cell A6.
   2. Save and close the template.
   3. Create a new workbook from the template and enable the macros.
   4. Name the macro-enabled workbook [your initials] Excel 8-7 and save it in your usual folder.
8. Run macros to complete the workbook.
   1. Click the PO # button, type BLS0057 and click OK.
   2. Click the Date button.
   3. Complete information as shown in Figure 8-74 and the table.Cell referenceCell valueB11General StoresB126000 North Western AvenueB13Springfield MO 65801A20Gold GTX Hybrid 26″B20BLS1212C202D20419A21Adult HelmetB21BLS2400C214D2139.99
   4. Save and close your workbook.
9. Move the template from the Custom Office Templates folder to your folder and hide the Developer tab if instructed to do so.