Business Finance
Grossmont College Computing The Values Using Excel Formulas Exam Practice

Grossmont College

Question Description

I’m studying and need help with a Excel question to help me learn.

please these two, with the instructions in the word doc, please these two, with the instructions in the word doc.

Unformatted Attachment Preview

First Project requirement8-1 Guided Project 8-1 Windows Mac Boyd Air uses a macros-only workbook and a related template. You edit and record macros in the macros-only workbook. In the template, you record a macro and assign it to a Button form control. [Student Learning Outcomes 8.1, 8.2, 8.3, 8.4, 8.5, 8.6, 8.7] Files Needed: BoydAir-08.xlsx (Available from the Start File link.) and BoydAirMacros08 (Available from the Resources link.) Completed Project File Names: [your name]-BoydAir-08.xlsx Skills Covered in This Project • Set macro security options. • Use a macros-only workbook. • Edit a macro in the Visual Basic Editor. • Record macros. • Run macros. • Record a macro with relative references. • Save a macro-enabled template. • Assign macros to Button form controls. Figure 8-37 Workbook includes macros and documentation 1. Open the BoydAir-08 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. Set macro security and display the Developer tab. a. Click the Options command [File tab] and click Trust Center in the left pane. b. Click the Trust Center Settings button and click Macro Settings in the left pane. c. Verify or select Disable all macros with notification and click OK. d. Click Customize Ribbon in the left pane, display the Developer tab, and close Excel Options. 3. Open the BoydAirMacros-08 file downloaded from the Resources link. This file is an Excel macro-enabled workbook (Figure 8-37). Click the Enable Content button. 4. Return to the your.name.BoydAir-08 workbook opened at the beginning of the project. 5. Run and edit a macro to insert today’s date. a. Press Ctrl+Shift+D to run the InsertDate macro. Today’s date displays in cell E1. b. Delete the contents of cell E1 and press Ctrl+Home. c. Click the Macros button [Developer tab, Code group]. d. Verify that the 'BoydAirMacros-08.xlsm'!InsertDate macro name is selected and click Edit to open the Visual Basic Editor (VBE) window. (If the Code window is not displayed, select Module1 in the Project Explorer pane and then choose Code from the View menu.) e. Click between the “H” and the “1” in the second comment line and type 3 so that the address is H31 as the cell reference. Figure 8-38 Edited code for the macro f. Edit the code Range(“E1”).Select to show H31. g. Edit the last line of code to show H32 instead of “E2” (Figure 8-38). h. Click the Close button at the top-right corner of the VBE window. i. Click the Macros button [Developer tab, Code group]. j. Verify that 'BoydAirMacros-08.xlsm'!InsertDate is selected and click Run. The date displays in cell H31. 6. Record a macro to insert text. a. Click the Record Macro button [Developer tab, Code group]. b. Type DateText in the Macro name box. c. Click the Shortcut key box, press Shift, and type t. d. Verify or choose This Workbook in the Store macro in box. e. Click the Description box, type Insert date text, and click OK. f. Select cell G31. g. Click the Align Right button [Home tab, Alignment group]. h. Type This report was run on and press Enter. i. j. Click the Stop Recording button to the right of the Ready indicator in the Status bar. The ScreenTip for the button is A macro is currently recording. Click to stop recording. Delete the contents of cells G31:H31. 7. Record a macro with relative references. a. Click the Use Relative References button [Developer tab, Code group] to enable the command. b. Select cell A5 and click the Record Macro button [Developer tab, Code group]. c. Type ApplyFill in the Macro name box. d. Click the Shortcut key box, press Shift, and type f. e. Verify or choose This Workbook in the Store macro in box. f. Click the Description box, type Apply fill color to current row, and click OK. g. h. Select cells A5:H5. Click the Fill Color arrow [Home tab, Font group] and choose Green, Accent 6, Lighter 80% (tenth column). i. Press Home to return the insertion point to cell A5. j. Press down arrow two times to place the pointer in cell A7. k. Click the Stop Recording button to the right of the Ready indicator in the Status bar. 8. Test the macros. a. Click the Undo button in the Quick Access toolbar to remove the fill color. b. Click the Use Relative References button [Developer tab, Code group] to cancel the command. c. Select cell A6 and press Ctrl+Shift+F to test the ApplyFill macro. The fill applies to the current row. d. Press Ctrl+Shift+T to test the DateText macro. e. Press Ctrl+Shift+D to test the InsertDate macro. f. Clear the fill color from cells A6:H6 and delete the contents of cells G31:H31. 9. Complete documentation for the macros. a. Return to the BoydAirMacros-08 workbook. Right-click the Sheet1 worksheet tab and select Move or Copy. Make a copy of the sheet in the your.name.BoydAir08 workbook opened at the beginning of the project and place it before the FirstQtr sheet. b. Rename Sheet1 in the your.name.BoydAir-08 workbook as Documentation c. Select cell B3, type Apply fill color to current row, and press Enter. d. Type Shortcut: Ctrl+Shift+F in cell B4. e. Complete documentation for the DateText macro in cells A9:B10 as shown in Figure 8-39. Figure 8-39 Documentation for the macros f. Press Ctrl+Home. 10. Click the Save button in the Quick Access toolbar in the BoydAirMacros-08 file to resave the macros only workbook. IMPORTANT: Do not close your macro workbook. 11. Return to the your.name.BoydAir-08 workbook opened at the beginning of the project and click the FirstQtr worksheet tab. 12. Assign macros to Button form controls. a. Click the FirstQtr sheet if it is not already selected. Click the Insert Controls button [Developer tab, Controls group]. b. Click the Button (Form Control) command in the Form Controls category. c. Draw a Button control that covers cells J4:K4. d. Select DateText in the Assign Macro dialog box and click OK (Figure 8-40). Figure 8-40 Macros are in the macros-only workbook e. Right-click the Button control and choose Edit Text. f. Delete the default text and type Date Text as the label. g. Right-click the control and choose Exit Edit Text. h. i. j. Click the Insert Controls button [Developer tab, Controls group] and select the Button (Form Control) command. Draw a second Button control that covers cells J7:K7. Select 'BoydAirMacros-08.xlsm'!InsertDate in the Assign Macro dialog box and click OK. k. Right-click the control and choose Edit Text. l. Delete the default text and type Date. m. Right-click the control and choose Exit Edit Text. n. Draw a Button control that covers cells J10:K10 and assign the ApplyFill macro. Edit the button text to Apply Fill. 13. Size and align Button form controls. a. Click cell J2 to deselect the controls. b. Right-click the Date Text button to display its selection handles. c. Press Ctrl and right-click the Date control and then right-click the Apply Fill control. Three controls are selected. d. Click the Align button [Drawing Tools Format tab, Arrange group] and select Align Left. e. Click the Height box [Drawing Tools Format tab, Size group] and type 0.25. f. Click the Width box [Drawing Tools Format tab, Size group], type 1.25, and press Enter (Figure 8-41). Figure 8-41 Button controls aligned and sized g. Click cell J2 to deselect the controls. 14. Select and delete the data in cells A5:A27 and E5:H27. 15. Select cell A5. 16. Run macros. a. Click the Date Text button control. b. Click the Date button control. Widen column H as needed to display the date. c. Select cell A5 and type yesterday’s date in mm/dd/yyyy format. d. Type 8:10 am in cell E5 and 9:30 am in cell F5. e. Type Delayed in cell G5 and 55 in cell H5. f. Select cell A5 and click the Apply Fill button. g. 17. Click the Apply Fill button as many times as necessary to apply fill color to the oddnumbered rows up to and including row 27. Save the Excel workbook named your.name.BoydAir-08, for grading in SIMnet (Figure 842). Click Yes in the message box saying "The following features cannot be saved in macro-free workbooks: VB project". NOTE: When a macro from another workbook is assigned to a form control, a link is created between the other workbook (BoydAirMacros-08) and the current workbook (your.name.BoydAir08). After saving and closing the files, if you wish to reopen and edit the project files, remember to open both the your.name.BoydAir-08.xlsx and BoydAirMacros-08.xlsm files. When a dialog warning appears, click Update or Enable Content to update links. 18. Upload and save your (*.xlsx) project file. 19. Submit project for grading. Figure 8-42 Excel 8-1 worksheet completed Second one: Independent Project 8-5 These instructions are compatible with both Microsoft Windows and Mac operating systems. Courtyard Medical Plaza developed a trainer’s worksheet for personalized workout plans. You record and edit macros for a macros-only workbook and run the macros in the trainer’s workbook. [Student Learning Outcomes 8.1, 8.2, 8.4, 8.5, 8.7] File Needed: CourtyardMedical-08.xlsx (Available from the Start File link.) and Excel 85Macros.xlsm (Downloaded from the Resources link) Completed Project File Names: [your name]-CourtyardMedical-08.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 a macro in the Visual Basic Editor. 1. Open the CourtyardMedical-08 workbook and click the Enable Editing button. The file will be renamed automatically to include your name. 2. Open the Excel 8-5Macros.xlsm file downloaded from the Resources link. This file is an Excel macro-enabled workbook. Display the Developer tab and verify that macro security is set to Disable all macros with notification. 3. Activate the Use Relative References command and select cell A1. 4. Record a macro with relative references. a. Record a macro named Titles with a shortcut of Ctrl+Shift+T in This Workbook. b. Type Enter column titles for the Description and click OK. c. Type Activities and press the right arrow key. d. Type Calories and press the right arrow key. e. Type Times and press the right arrow key. Type Burned and press the right arrow key. Figure 8-58 Data is entered as macros are recorded f. Press the left arrow key four times to return to the “Activities” label. g. Select cells A1:D1 and apply bold and italic. h. Stop recording. i. Select cell A4 and record a macro with relative references named Activities with a shortcut of Ctrl+Shift+A in This Workbook. Type Enter activities for the Description. j. Type the labels shown here 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. Dance Fusion High Intensity Interval Pilates/Yoga Running Strength Training Swimming Water Aerobics Figure 8-59 Documentation for the macros workbook k. Stop recording (Figure 8-58). 5. Turn off Use Relative References. 6. Select cell F1 and type Titles Macro. 7. Remove bold and italic from cell F1. 8. Select cell F2 and type Ctrl+Shift+T. 9. Select cell F4 and type Activities Macro. 10. Type Ctrl+Shift+A in cell F5 (Figure 8-59). 11. Edit the Titles macro in the Visual Basic Editor to change the word “Activities” to Activity. 12. Correct other typing errors as needed. 13. 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-60). Figure 8-60 Print code from the File menu in the VBE 14. Close the Visual Basic Editor to return to the worksheet. 15. Edit cell A1 to display Activity. 16. Save the macros-only workbook and leave it open. 17. Return to the your.name-CourtyardMedical-08 file you opened in step 1. 18. Run the Titles macro. a. Click cell A3 and press Ctrl+Shift+T. b. Run the Titles macro starting in each of cells F3, A15, and F15. 19. Run the Activities macro. 20. a. Click cell A4 and press Ctrl+Shift+A. b. Run the Activities macro starting in each of cells F4, A16, and F16. Save and close the macros-only workbook (Excel 8-5Macros.xlsm). 21. Hide the Developer tab. Save and close the your.name-CourtyardMedical-09 workbook (Figure 8-61). 22. Upload and save your project file. 23. Submit project for grading. Figure 8-61 Excel 8-5 completed worksheet ...
Purchase answer to see full attachment
Student has agreed that all tutoring, explanations, and answers provided by the tutor will be used to help in the learning process and in accordance with Studypool's honor code & terms of service.

Final Answer

These are completed.

Activity

Calories

Dance Fusion
High Intensity Interval
Pilates/Yoga
Running
Strength Training
Swimming
Water Aerobics

Times

Burned

Titles Macro
Ctrl+Shift+T
Activi...

Sara_777 (1088)
UC Berkeley

Anonymous
I was on a very tight deadline but thanks to Studypool I was able to deliver my assignment on time.

Anonymous
The tutor was pretty knowledgeable, efficient and polite. Great service!

Anonymous
Heard about Studypool for a while and finally tried it. Glad I did caus this was really helpful.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4
Similar Questions
Related Tags