Excel Chapter 10

Customizing the Quick Access Toolbar 1. Add the macro ApplyDCTax to the Quick Access Toolbar for the current workbook only. 2. Reset just the Quick Access Toolbar to its original state.

1. Click the Customize Quick Access Toolbar button and select More Commands... The Excel Options dialog opens to the Customize the Quick Access Toolbar page. Expand the Choose commands from list and select Macros. Click ApplyDCTax and click the Add button. Click OK. 2. Click the Customize Quick Access Toolbar button and select More Commands... Click the Reset button and select Reset only Quick Access Toolbar. Click Yes.

Modifying a Macro Using VBA 1. Edit the code for the FormatData macro in the Visual Basic Editor. 2. Modify the code to select columns D:F with a single command and then apply the Currency style. (Hint: You should end up with two lines of code instead of four.) Save your changes. 3. Modify the code to apply the Comma style. Save your changes. 4. Modify the code to enter the text Inventory ID instead of Item ID. Save your changes. 5. Insert a new comment: Change column heading. 6. Save the Changes and close the Visual Basic Editor.

1. Click the Developer tab. In the Code group, click the Macros button. Select FormatData and click the Edit button. 2. In the code Columns("D:D"). Select change D:D to D:F and then delete the lines of code Columns("E:F"). Select and next line Selection.Style = "Currency". On the Visual Basic Editor toolbar, click the Save button. 3. In the line of code Selection.Style= "Currency" change Currency to Comma. On the Visual Basic Editor toolbar, click the Save button. 4. In the code ActiveCell.FormulaR1C1 = "Item ID" change Item ID to Inventory ID. On the Visual Basic Editor toolbar, click the Save button. 5. Type 'Change column heading and then press Enter.

Opening a Macro-Enabled Workbook 1. Enable the active content in this workbook for just this session. 2. Always enable this workbook's active content by making it a trusted document.

1. Click the Enable Content button in the Message Bar. 2. Click the File tab. Click the Enable Content button and select Enable All Content.

Adding the Developer Tab to the Ribbon 1. Add the Developer tab to the Ribbon.

1. Click the File tab. Click Options to open the Excel Options dialog. Click Customize Ribbon. At the right side of the Customize the Ribbon window, the Customize the Ribbon list shows all the tab and groups available. Click the Developer check box to add the Developer tab to the Ribbon. Click OK.

Adding a Custom Tab to the Ribbon 1. Add a custom tab named My Macros to the Ribbon. 2. Rename the new group Tax Macros and use the first icon in the list as the group icon. 3. Move the new My Macros tab so it appears first in the list. 4. Add the macro ApplyDCTax to the Tax Macros group. 5. Apply the changes to the Ribbon. 6. Remove all customization and set the Ribbon back to its original state.

1. Click the File tab. Click Options to open the Excel Options dialog. Click Customize Ribbon. Click the New Tab button. Click New Tab (Custom) to select it. Click the Rename button. Type My Macros. Click OK. 2. Click New Group (Custom) to select it. Click the Rename button. Type Tax Macros. Click the first icon to select it. Click OK. 3. Click My Macros (Custom) to select it. Click the Move up button. 4. Click the Tax Macros (Custom) group. Expand the Choose Commands From list and select Macros. Click ApplyDCTax and click the Add button. 5. Click Ok 6. Click the File tab. Click Options to open the Excel Options dialog. Click Customize Ribbon. Click the Reset button and select Reset all Customizations. Click Yes.

Saving a Macro-Enabled Workbook or Template 1. Save this file as a macro-enabled template. 2. Save this file as a macro enabled workbook.

1. Click the File tab. Click Save As. On the Save As page, expand the list of file types and select Excel Macro-Enabled Template. Click Save. 2. Click the File tab. Click Save As. On the Save As page, expand the list of file types select Excel Macro Enabled Workbook. Click Save.

Changing the Trust Center Settings 1. Open the Trust Centre and change the setting to disable all macros and display a warning in the Message Bar so you can choose to enable them on a case-by-case basis. 2. Add the folder named Trusted Files as a new trusted location. Close the Excel Options dialog when you are finished.

1. Click the File tab. Click the Options button to open the Excel Options dialog. Click Trust Center, and then click the Trust Center Settings button. Click Macro Settings. Click the Disable all macros with notification radio button and click OK. Click OK to close the Excel Options dialog. 2. Click the File tab. Click the Options button to open the Excel Options dialog. Click Trust Center, and then click the Trust Center Settings button. click Trusted Locations. Click the Add new location button. Click the Browse button. Click the Trusted Files Folder. Click OK. Click OK. Click OK to close the Trust Center. Click OK again to close the Excel Options dialog.

Running a Macro 1. Run the FormatImport macro.

1. On the Developer tab, in the Code group, click the Macros button. In the Macro dialog, FormatImport is selected. Click the Run button.

Recording a Macro 1. Begin recording a new macro. Name the new macro AddSubtotals. Assign the keyboard shortcut Ctrl+Shift+S. Add the description: Sort by last name and then add subtotals, and then begin recording the macro. The macro should be stored in the current workbook. 2. Sort the data from A-Z by the Last Name column by clicking the Data tab, and then in the Sort & Filter group, click the AZ button. 3. Add subtotal: at every change in Last Name, Sum the values in the Price column.. On the Data tab, in the Outline group, click the Subtotal button. Click OK. 4. Stop recording the macro now.

1. On the Developer tab, in the Code group, click the Record Macro button. Type AddSubtotals in the Macro name box. Press the Shift key and type S in the Shortcut key box. Type Sort by last name and then add subtotals in the Description box. Click OK. 2. Click the Data tab, and then in the Sort & Filter group, click the AZ button. 3. On the Data tab, in the Outline group, click the Subtotal button. Click the Price check box to add a checkmark. Click the Location checkbox to remove the checkmark. Click OK. 4. Click the Developer tab. In the Code group, click the Stop Recording button.

Adding a Macro to a Form Button Control 1. Add a form control button to run the ApplyDCTax macro. The button should be placed at approximately cells D1:E2. 2. Change the button text to: Show DC Tax.

1. On the Developer tab, in the Controls group, click the Insert Controls button. Click the button (form control) button. Click and drag from cell D1 to E2 to draw the button. Click ApplyDCTax. Click Ok. 2. Right-click the button and select Edit Text. Edit the button text to Show DC Tax and then click anywhere outside the button.