1 / 95

Excel Chapter 7

Excel Chapter 7. Using Macros and Visual Basic for Applications (VBA) with Excel. Objectives. Use passwords to assign protected and unprotected status to a worksheet Use the macro recorder to create a macro Execute a macro and view and print code for a macro

mort
Download Presentation

Excel Chapter 7

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Excel Chapter 7 Using Macros and Visual Basic for Applications (VBA) with Excel

  2. Objectives • Use passwords to assign protected and unprotected status to a worksheet • Use the macro recorder to create a macro • Execute a macro and view and print code for a macro • Understand Visual Basic for Applications (VBA) code and explain event-driven programs • Customize the Quick Access Toolbar by adding a button Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  3. Objectives • Add controls, such as command buttons, scroll bars, check boxes, and spin buttons to a worksheet • Assign properties to controls • Use VBA to write a procedure to automate data entry into a worksheet • Understand Do-While and If-Then-Else statements • Review a digital signature on a workbook Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  4. Plan Ahead • Ascertain the steps needed to create a macro • Create the user interface. In this chapter, two separate user interfaces are created that automate the entry of the employee’s investment model data • Determine properties for controls in the user interface • Establish the Visual Basic code needed for each control • Evaluate the completed user interface through testing Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  5. Starting Excel and Opening a Workbook • Start Excel • Open the file Caliber from the Data Files for Students and then save the workbook using the file name, Caliber Steel • If the Excel window is not maximized, double-click its title bar to maximize it • If the worksheet window in Excel is not maximized, click the Maximize button next to the Close button on its title bar to maximize the worksheet within Excel Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  6. Starting Excel and Opening a Workbook Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  7. Entering Data in a Selected Range of Cells • Select the range D5:D10. In cell D5, type Ali Kunar as the employee name and then press the ENTER key • In cell D6, type 68400 as the annual salary and then press the ENTER key • In cell D7, type 9% as the employee investment percentage and then press the ENTER key • In cell D8, type 6% as the employer match percentage and then press the ENTER key • In cell D9, type 8% as the annual return and then press the ENTER key • In cell D10, type 25 as the years of service and then press the ENTER key to display the new future value of $813,127.57 in cell F10 • Click cell H5 to remove the selection from the range D5:D10. You may have to scroll down the worksheet to refresh the pointer in the range B16:B24 Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  8. Entering Data in a Selected Range of Cells Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  9. Undoing a Group of Entries Using the Undo Button • Click the Undo button arrow on the Quick Access Toolbar • When the Undo list appears, drag from the top down through Ali Kunar and then release the left mouse button to change the range D5:D10 back to its original values. Click cell H5. You may have to scroll down the worksheet to refresh the pointer in the range B16:B24 Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  10. Undoing a Group of Entries Using the Undo Button Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  11. Un-protecting a Password-Protected Worksheet • Click the Review tab on the Ribbon • Click the Unprotect Sheet button to display the Unprotect Sheet dialog box • When the Unprotect Sheet dialog box appears, type caliber in the Password text box • Click the OK button to unprotect the 401(k) Investment Model worksheet Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  12. Un-protecting a Password-Protected Worksheet Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  13. Displaying the Developer Tab, Enabling Macros, and Saving a Workbook as a Macro-Enabled Workbook • Click the Office Button to display the Office Button menu and then the Excel Options button to display the Excel Options dialog box • Click the Show Developer tab in the Ribbon check box • Click the OK button in the Excel Options dialog box to display the Developer tab on the Ribbon. • Click the Developer tab • Click the Macro Security button on the Ribbon to display the Trust Center dialog box • When the Trust Center dialog box appears, click the ‘Enable all macros’ option button to select it Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  14. Displaying the Developer Tab, Enabling Macros, and Saving a Workbook as a Macro-Enabled Workbook • Click the OK button to close the Macro Security dialog box • Click the Office Button and then click Save As to display the Save As dialog box • When the Save As dialog box appears, click the ‘Save as type’ box arrow and then click Excel Macro-Enabled Workbook in the ‘Save as type‘ list • Click the Save button in the Save As dialog box to save the workbook as an Excel Macro-Enabled Workbook file type Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  15. Displaying the Developer Tab, Enabling Macros, and Saving a Workbook as a Macro-Enabled Workbook Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  16. Recording a Macro to Print the Worksheet in Portrait Orientation Using the Fit to Option • If necessary, click the Developer tab on the Ribbon • Click the Record Macro button on the Ribbon to display the Record Macro dialog box • When the Record Macro dialog box appears, type PrintPortrait in the Macro name text box • Type r in the Shortcut key text box to set the shortcut key for the macro to CTRL+R • Make sure the ‘Store macro in‘ box displays This Workbook and then type Macro prints worksheet in portrait orientation on one page in the Description text box Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  17. Recording a Macro to Print the Worksheet in Portrait Orientation Using the Fit to Option • Click the OK button to begin recording the macro and cause the Record Macro button to become the Stop Recording button • Click the Page Layout tab on the Ribbon • Click the Page Setup Dialog Box Launcher to display the Page Setup dialog box • When the Page Setup dialog box displays, click the Page tab. If necessary, click Portrait in the Orientation area, and then click Fit to in the Scaling area • Click the Print button in the Page Setup dialog box Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  18. Recording a Macro to Print the Worksheet in Portrait Orientation Using the Fit to Option • When the Print dialog box displays, click the OK button to print the worksheet • Click the Page Setup Dialog Box Launcher to display the Page Setup dialog box • If necessary, when the Page Setup dialog box displays, click the Page tab; click Landscape in the Orientation area; click Adjust to in the Scaling area; and then, if necessary, type 100 in the % normal size box • Click the OK button to close the Page Setup dialog box • Click the Developer tab on the Ribbon and then click the Stop Recording button to stop recording the worksheet activities Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  19. Recording a Macro to Print the Worksheet in Portrait Orientation Using the Fit to Option Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  20. Password-Protecting the Worksheet, Saving the Workbook and Closing the Workbook • Click the Review tab on the Ribbon, and then click the Protect Sheet button on the Ribbon. When the Protect Sheet dialog box appears, type caliber in the ‘Password to unprotect sheet’ text box and then click the OK button. When the Confirm Password dialog box is displayed, type caliber and then click the OK button • Click the Office Button and then click Save As. When the Save As dialog box is displayed, type Caliber Steel1 in the File name text box. Make sure UDISK (E:) displays in the Save in box and then click the Save button in the Save As dialog box • Click the workbook’s Close button on the right side of its menu bar to close the workbook and leave Excel active Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  21. Setting the Macro Security Level to Medium • Click the Developer tab on the Ribbon • Click the Macro Security button on the Ribbon to display the Trust Center dialog box • Click the ‘Disable all macros with notification’ option button • Click the OK button in the Trust Center dialog box to close it Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  22. Setting the Macro Security Level to Medium Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  23. Opening a Workbook with a Macro and Executing the Macro • With Excel active, open the Caliber Steel1 workbook • Click the Options button in the Security Warning box to display the Microsoft Office Security Options dialog box • Click the ‘Enable this content’ option button to select it • Click the OK button • When the CaliberSteel1 workbook opens, press CTRL+R to run the macro and print the worksheet Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  24. Opening a Workbook with a Macro and Executing the Macro Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  25. Viewing and Printing a Macro’s VBA Code • Click the Developer tab on the Ribbon • Click the Macros button on the Ribbon to display the Macro dialog box • If necessary, when the Macro dialog box is displayed, click PrintPortrait in the Macro name list • Click the Edit button to start the Microsoft Visual Basic Editor • Use the scroll bar to scroll through the VBA code Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  26. Viewing and Printing a Macro’s VBA Code • When you are finished, click File on the menu bar • Click Print • When the Print – VBA Project dialog box is displayed, click the OK button to print the macro code • Click the Microsoft Visual Basic Editor Close button on the right side of the title bar Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  27. Viewing and Printing a Macro’s VBA Code Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  28. Adding a Button to the Quick Access Toolbar, Assigning the Button a Macro, and Using the Button • Right-click anywhere on the Quick Access Toolbar to display the shortcut menu • Click the Customize Quick Access Toolbar command on the shortcut menu to display the Customize the Quick Access Toolbar page of the Excel Options dialog box • Click the ‘Choose commands from’ box arrow • Click Macros in the ‘Choose commands from’ list to display a list of macros • If necessary, click the PrintPortait macro to select it • Click the Add button to add the PrintPortrait macro to the Customize Quick Access Toolbar list Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  29. Adding a Button to the Quick Access Toolbar, Assigning the Button a Macro, and Using the Button • Click the Modify button to display the Modify Button dialog box • Click the printer icon (column 5, row 2) in the Symbol list • Click the OK button to assign the printer icon to the new command and to close the Modify Button dialog box • Click the OK button in the Excel Options dialog box to close it • Point to the PrintPortrait button on the Quick Access Toolbar to display the ScreenTip for the button Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  30. Adding a Button to the Quick Access Toolbar, Assigning the Button a Macro, and Using the Button Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  31. Un-protecting a Password-Protected Worksheet • With the Caliber Steel1 workbook open, click the Review tab on the Ribbon, and then click the Unprotect Sheet button on the Ribbon • When the Unprotect Sheet dialog box appears, type caliber as the password and then click the OK button to unprotect the worksheet Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  32. Adding a Command Button Control to the Worksheet • Click the Developer tab on the Ribbon • Click the Insert button on the Ribbon to display the Controls gallery • Click the Command Button button in the ActiveX area of the Controls gallery (column 1, row 1 of ActiveX area) to switch to Design mode • Move the mouse pointer (a crosshair) to the upper-left corner of cell H5 • Drag the mouse pointer so the rectangle defining the button area appears and hold • Release the left mouse button to add the Command Button control with the default caption CommandButton1 Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  33. Adding a Command Button Control to the Worksheet Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  34. Setting the Command Button Control Properties • With the Command Button control selected and Excel in Design mode, click the Properties button on the Ribbon to open the Properties window for the Command Button control • If necessary, when the Properties window opens, click the Alphabetic tab, click Caption in the Properties list, and then type New Data as the caption • Click ForeColorin the Properties list, click the ForeColorarrow, and then click the Palette tab Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  35. Setting the Command Button Control Properties • Click blue (column 7, row 3) on the ForeColor palette • Click Font in the Properties list and then click the Font button • When the Font dialog box appears, click Bold in the Font style list and 12 in the Size list • Click the OK button to display the Command Button control. If necessary, drag the lower-right corner of the Properties window to make all of the properties display in the window Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  36. Setting the Command Button Control Properties Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  37. Entering the New Data Button Procedure Using the Visual Basic Editor • With the New Data button selected and Excel in Design mode, point to the View Code button on the Ribbon • Click the View Code button on the Ribbon to start the Microsoft Visual Basic Editor • When the Microsoft Visual Basic Editor starts, if the Project Explorer window appears on the left, click its Close button • If necessary, double-click the title bar to maximize the Microsoft Visual Basic Editor window • If necessary, double-click the title bar of the Microsoft Visual Basic window to maximize it Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  38. Entering the New Data Button Procedure Using the Visual Basic Editor • Click to the left of the letter P in the word Private on the first line and press the ENTER key to add a blank line before the Sub statement • Move the insertion point to the blank line and then type the seven comment statements (lines 1 through 7) in Table 7–2 on page EX 558. Make sure you enter an apostrophe (‘) at the beginning of each comment line • Press the ENTER key to start a new line • Position the insertion point on the blank line between the Sub and End Sub statements Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  39. Entering the New Data Button Procedure Using the Visual Basic Editor • Enter lines 9 through 16 in Table 7–2. For clarity, indent all lines between the Sub statement and End Sub statement by three spaces • Click the Close button on the right side of the Microsoft Visual Basic title bar to return to the worksheet • Click the Close button on the right side of the Properties window • Click the Design Mode button on the Ribbon to return to Run mode Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  40. Entering the New Data Button Procedure Using the Visual Basic Editor Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  41. Entering 401 (k) Investment Model Data Using the New Data Button • Click the New Data button • When Excel displays the first Enter dialog box with the prompt message, Employee Name?, type Millie Jenkins in the text box • Click the OK button or press the ENTER key • When Excel displays the next Enter dialog box with the prompt message, Annual Salary?, type 87200 in the text box • Click the OK button • When Excel displays the next Enter dialog box with the prompt message, Employee Investment in %?, type 6% in the text box, making certain to type the percent (%) sign Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  42. Entering 401 (k) Investment Model Data Using the New Data Button • Click the OK button • When Excel displays the next Enter dialog box with the prompt message, Employer Match in %?, type 3% in the text box • Click the OK button • When Excel displays the next Enter dialog box with the prompt message, Annual Return in %?, type 9% in the text box • Click the OK button to assign 9.00% to cell D9 • When Excel displays the next Enter dialog box with the prompt message, Years of Service?, type 20 in the text box • Click the OK button to display the results for Millie Jenkins in the range F6:F10 Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  43. Entering 401 (k) Investment Model Data Using the New Data Button Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  44. Protecting a Worksheet and Saving the Workbook • Click the Review tab on the Ribbon, and then click the Protect Sheet button on the Ribbon • When the Protect Sheet dialog box appears, type caliber in the ‘Password to unprotect sheet’ text box and then click the OK button. When the Confirm Password dialog box appears, type caliber and then click the OK button • Click the Office Button and then click Save As on the Office Button menu. When the Save As dialog box is displayed, type Caliber Steel2 in the File name text box. Make sure UDISK 2.0 (E:) displays in the Address bar and then click the Save button • Click the Close button on the right side of the title bar to close the Caliber Steel2 workbook Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  45. Opening a Workbook and Un-protecting a Worksheet • Open the Caliber Steel1 workbook saved in Phase 1 of this chapter • Click the Options button in the Security Warning box. When the Microsoft Office Security Options dialog box appears, click the ‘Enable this content’ option button and then click the OK button to close the dialog box • Click the Office Button and then click Save As on the Office Button menu. When the Save As dialog box is displayed, type Caliber Steel3 in the File name text box and then click the Save button in the Save As dialog box • Click the Review tab on the Ribbon and then click the Unprotect Sheet button on the Ribbon. When the Unprotect Sheet dialog box appears, type caliber in the Password text box, and then click the OK button to unprotect the worksheet Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  46. Adding Controls to a User Interface • Select the range H5:L24 • Click the Fill Color button arrow on the Home tab on the Ribbon and then click White, Background 1, Darker 25% (column 1, row 4) on the Fill Color palette • Click the Borders button arrow on the Ribbon and then click Thick Box Border on the Borders palette • Click the Developer tab on the Ribbon • Click the Insert button on the Ribbon, click the Command Button button in the ActiveX area in the Controls gallery, and then drag the mouse pointer so the control displays Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  47. Adding Controls to a User Interface • Click the Insert button on the Ribbon, click the Scroll Bar button in the ActiveX area in the Controls gallery, and then move the mouse pointer to approximately the left of cell I12 • Drag the mouse pointer so the Scroll Bar control displays • Point to the Scroll Bar control • Hold down the CTRL key and drag a copy of the Scroll Bar control to the location. It is important that you release the left mouse button before you release the CTRL key • Click the Insert button on the Ribbon, click the Check Box button in the ActiveX area in the Controls gallery, and then move the mouse pointer to the upper-left corner of the location of the Check Box control • Drag the mouse pointer so the rectangle defining the Check Box control area displays with the word CheckBox1 showing Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  48. Adding Controls to a User Interface • Click the Insert button on the Ribbon, click the Spin Button button in the ActiveX area in the Controls gallery, and then move the mouse pointer to the upper-left corner of the location of the Spin Button control • Drag the mouse pointer so the rectangle defining the Spin Button control area displays • Point to the Spin Button control in the Personalization Center • Hold down the CTRL key and drag a copy of the Spin Button control to the second location. It is important that you release the left mouse button before you release the CTRL key • Click the Insert button on the Ribbon, click the Label button in the ActiveX area in the Controls gallery, and then move the mouse pointer to the left of the first Scroll Bar control below the CommandButton1 button Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  49. Adding Controls to a User Interface • Drag the mouse pointer so the rectangle defining the Label control displays • Point to the Label control • Hold down the CTRL key and then drag a copy of the Label control created in Step 7 to the center of cell J3. While holding down the CTRL key, drag the newly copied Label control to the next location where a Label control is required. • Continue in this fashion until you have a total of 11 Label controls Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

  50. Adding Controls to a User Interface Microsoft Office 2007: Comprehensive Concepts and Techniques - Windows Vista Edition

More Related