Excel Name Tag Macros

Please see the 2 attached files. (Word and Excel) 

One is an instruction sheet, the second is the work that needs done.

The work that is being submitted needs to be done in Excel 2010

Ive done most of this work myself already but am getting hung up in a couple areas.

The file I am submitting is a clean copy, no work has been done on it.

 

The work is due at 11pm Eastern Standard Time

 

This should be a quick turn around for this type of work.

Office 2010 – myitlab:grader – Instructions GO! Excel Chapter 9 – EOC project

Name Tag Macros

Project Description:
In this project, you will create two macros in the workbook that each set a specific print area to prepare for printing name tags. One macro will have a keyboard shortcut associated with it, and the other will run from a command button. You will copy the finalized macro code into a blank sheet in the workbook, and then save the macro-enabled workbook in the Excel Workbook (.xlsx) format.

Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step Instructions Points Possible
1 Start Excel. Download, save, and open the Excel macro-enabled workbook named GO_e09_Grader_EOC.xlsm. Ensure that the Developer tab is enabled. If necessary, click the Enable Content button in the Security message bar. 0
2 With cell A1 selected, begin to record a macro. Name the macro as TreeNameTag and assign CTRL+n as the Shortcut key. Do not close the Macro dialog box. 8
3 Store the macro in this workbook, and enter the text Macro for name tags as the description. Close the dialog box. While recording the macro, be sure to follow the steps exactly as specified. Do not switch between windows or worksheets in between steps. 8
4 In Sheet 1, in recording mode, select the range B4:F15. 8
5 Set the print area to print this range only, and then stop recording the macro. Print preview the name tag. Clear the print area from B4:F15. Return to cell A1 and then press CTRL+n to run the macro and ensure that the name tag print previews as expected. 8
6 In the Microsoft Visual Basic for Applications window, delete any empty comment lines or blank lines in the code, and then copy the entire TreeNameTag macro including the Sub statement to the End Sub statement. Paste the code into cell A1 of Sheet2. The VBA code should appear in cells A1:A7 when pasted. 12
7 In Sheet1, draw a command button from cells I17:K18. Using the Properties pane, set the name of the button to LeafNameTag, the height to 23.25, and the width to 150. Set the caption to Print Leaf Name Tag. 10
8 From the Microsoft Visual Basic for Applications window, copy the five lines of the TreeNameTag macro between Sub statement and the End Sub statement. From Excel, view the code for the Print Leaf Name Tag button. In the Microsoft Visual Basic for Applications window, click the General down arrow and then click LeafNameTag. 7
9 Immediately below the Private Sub LeafNameTag_Click() statement, paste the five lines of copied code.
Replace the comment text TreeNameTag Macro with LeafNameTag Command Button. 8
10 Replace the comment text Macro for name tags with Prints Leaf Name Tag. Delete the entire comment line containing Keyboard Shortcut: Ctrl + n (including the comment character). Be sure to delete any remaining empty comment lines or blank lines in the code. 8
11 In the Range statement, modify the range as “H4:L15”. In the following line that sets the print area, modify the range as “$H$4:$L$15”.
Return to Excel. Deselect Design Mode for the button. Click the Print Leaf Name Tag button to run the macro. Ensure that the name tag print previews as expected. 11
12 From the Microsoft Visual Basic for Applications window, copy the entire LeafNameTag macro from the Sub statement to the End Sub statement inclusive (six lines). Paste the code into cell A12 of Sheet2 in this workbook (the VBA code will appear in cells A12:A17 when pasted).
In Sheet1, clear the print area from H4:L15 and select cell A1. Test the two macros to be sure that they work as expected. 12
13 Save the macro-enabled workbook as an Excel workbook, using the same file name. Click Yes to save it as a macro-free workbook. Close the workbook. Exit Excel. Submit the GO_e09_Grader_EOC.xlsx workbook as directed. 0
Total Points 100

Updated on: 12/28/2010 1 E_CH09_GOCOMP_EOC_Instructions x

Sheet1

Name______________________________

Name______________________________

Pine Valley
Human Resources
Consulting

Pine Valley
Human Resources
Consulting

Sheet2