Independent Project 8-6

Independent Project 8-6

Classic Gardens and Landscapes (CGL) uses a macro-enabled template to assess work hours needed and the number of staff available. You build a macro to insert a date statement, assign it to a form control, and insert an ActiveX control.

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

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

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

Skills Covered in This Project

• Set macro security options.
• Insert a Button form control.
• Record and run a macro.
• Assign a macro to a button.
• Insert an ActiveX Spin Button control.
• Edit properties for an ActiveX control.
• Save a macro-enabled template.

1. Set macro security to Disable VBA macros with notification and display the Developer tab.
2. Open the ClassicGardens-08 workbook from your student data files and save it as a macro-enabled template named [your initials] Excel 8-6Template in the Custom Office Templates folder. Leave the template open.
3. Record a macro to insert the date statement.
   1. Record a macro with absolute references in this workbook named DateStatement. Do not use a shortcut or a description.
   2. Select cell A7. This is a merged cell.
   3. Start with a CONCAT function to display “Week of ” as the initial text.
   4. Nest a TEXT function that displays today’s date in mmmm dd, yyyy format (Figure 8-70).
   5. Stop recording.
   6. Delete the text that was entered while recording and test your macro. If it does not execute properly, delete the macro and try again.
   7. Finish with no text in cell A7, but do not clear the formatting in the cell.
4. Insert a Button form control.
   1. Insert an Excel Button (Form Control) that covers cells J1:J2 and assign the DateStatement macro.
   2. Edit the text to Insert Date.
   3. Format the control to use a bold font.
   4. Test the button and then delete the date statement in cell A7.
5. Insert an ActiveX control.
   1. Draw a Spin Button (ActiveX Control) that covers cells E20:E23 (Figure 8-71). When you draw a spin button control that is taller than it is wide, the buttons face up and down.
   2. Display the Properties window for the control.
   3. Set the Linked Cell to cell D21.
   4. Confirm that the Min setting is 0.
   5. Include a Shadow for the control.
   6. Turn off Design Mode.
   7. Select cell A6.
6. Save the macro-enabled template and close it.
7. Create a workbook from the template.
   1. Create a new workbook from [your initials] Excel 8-6Template and enable the macros.
   2. Click the Insert Date button.
   3. Click the Spin Button control to display 12.
8. Save the workbook as an Excel Macro-Enabled Workbook named [your initials] Excel 8-6 in your usual location for saving files (Figure 8-72).
9. Close the workbook.
10. Move the template from the Custom Office Templates folder to your folder and hide the Developer tab if instructed to do so.