Excel Project 7

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

2. Objectives • Use the Undo button to undo multiple changes • 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

3. Objectives • Customize a toolbar by adding a button • Customize a menu by adding a command • 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

4. Objectives • Use VBA to prompt a worksheet user for input and display messages • Understand Do-While and If-Then-Else statements • Test and validate incoming data • Review a digital certificate on a workbook

5. Undoing a Group of Entries Using the Undo Button • Start Excel, open, and make the necessary adjustments to the workbook as described on pages EX 485 through EX 487 • Click the Undo button arrow on the Standard toolbar • When the Undo list appears, drag from the top down through Marilee Lem and then release the left mouse button. Click cell H5

6. Unprotecting a Password-Protected Worksheet • Click Tools on the menu bar, point to Protection, and then point to Unprotect Sheet on the Protection submenu • Click Unprotect Sheet • When the Unprotect Sheet dialog box appears, type cartydollars in the Password text box • Click the OK button

7. Unprotecting a Password-Protected Worksheet

8. Recording a Macro to Print the Worksheet in Portrait Orientation Using the Fit to Option • Click Tools on the menu bar • Point to Macro on the Tools menu • Click Record New Macro • When the Record Macro dialog box displays, type PrintPortrait in the Macro name text box • Type r in the Shortcut key text box and then type Macro prints worksheet in portrait orientation on one page in the Description text box. Make sure the Store macro in box displays This Workbook

9. Recording a Macro to Print the Worksheet in Portrait Orientation Using the Fit to Option • Click the OK button • If the Stop Recording toolbar does not display, click View on the menu bar, then click Stop Recording on the Toolbars submenu • Click File on the menu bar and then click Page Setup • When the Page Setup dialog box appears, 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

10. 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 • Click File on the menu bar and then click Page Setup • 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 type 100 in the % normal size box • Click the OK button • Click the Stop Recording button

11. Recording a Macro to Print the Worksheet in Portrait Orientation Using the Fit to Option

12. Password-Protecting the Worksheet, Saving the Workbook, and Closing the Workbook • Click Tools on the menu bar, point to Protection, and then click Protect Sheet on the Protection submenu. When the Protect Sheet dialog box appears, type cartydollars in the Password to unprotect sheet text box and then click the OK button. When the Confirm Password dialog box displays, type cartydollars and then click the OK button • Click File on the menu bar and then click Save As. When the Save As dialog box displays, type Carty Financial1 in the File name text box. Make sure 3½ Floppy (A:) 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

13. Opening a Workbook with a Macro and Executing the Macro • With Excel active, click File on the menu bar and then click Open • When the Open dialog box displays, click the Look in box arrow, and then click 3½ Floppy (A:) • Double-click the file name Carty Financial1 • Click the Enable Macros button • When the Carty Financial1 workbook opens, press CTRL+R

14. Opening a Workbook with a Macro and Executing the Macro

15. Viewing and Printing a Macro’s VBA Code • Click Tools on the menu bar • Click Macros in the Tools menu • If necessary, when the Macro dialog box appears, click PrintPortrait in the list • Click the Edit button • If necessary, when the Visual Basic Editor window opens, close the Project and Properties windows

16. Viewing and Printing a Macro’s VBA Code • Use the scroll bar to scroll through the VBA code • When you are finished, click File on the menu bar • Click the Print command • When the Print - VBA Project dialog box displays, click the OK button • Click the Visual Basic Editor Close button on the right side of the title bar

17. Viewing and Printing a Macro’s VBA Code

18. Adding a Button to a Toolbar, Assigning the Button a Macro, and Using the Button • Click Tools on the menu bar • Click Customize • When the Customize dialog box opens, click the Commands tab • Scroll down in the Categories list and then click Macros • Drag the Custom Button with the smiley face image from the Commands list to the immediate left of the Microsoft Excel Help button on the Standard toolbar

19. Adding a Button to a Toolbar, Assigning the Button a Macro, and Using the Button • Right-click the button with the smiley face image on the Standard toolbar • When the shortcut menu appears, type Print Portrait in the Name text box • Click the Change Button Image on the shortcut menu • Click the open book image (row 6,column 1) • Right-click the button with the open book image on the Standard toolbar

20. Adding a Button to a Toolbar, Assigning the Button a Macro, and Using the Button • Click Assign Macro on the shortcut menu • When the Assign Macro dialog box appears, click PrintPortrait in the Macro name list • Click the OK button • Click the Close button in the Customize dialog box • Click the Print Portrait button on the Standard toolbar

21. Adding a Button to a Toolbar, Assigning the Button a Macro, and Using the Button

22. Adding a Command to a Menu, Assigning the Command a Macro, and Using the Command • Click Tools on the menu bar and then click Customize • If necessary, when the Customize dialog box appears, click the Commands tab • Scroll down in the Categories box and then click Macros • Click File on the menu bar to display the File menu • Drag the Custom Menu Item entry from the Commands list in the Customize dialog box immediately below the Print command on the File menu

23. Adding a Command to a Menu, Assigning the Command a Macro, and Using the Command • Right-click the Custom Menu Item command on the File menu and then click the Name text box on the shortcut menu • Type Print Po&rtrait Ctrl+R in the Name text box • Click Assign Macro on the shortcut menu • When the Assign Macro dialog box displays, double-click PrintPortrait

24. Adding a Command to a Menu, Assigning the Command a Macro, and Using the Command • Click the Close button at the bottom of the Customize dialog box • Click File on the menu bar • Click Print Portrait on the File menu • Click the Save button on the Standard toolbar to save the workbook using the file name Carty Financial1

25. Adding a Command to a Menu, Assigning the Command a Macro, and Using the Command

26. Unprotecting a Password-Protected Worksheet • With the Carty Financial1 workbook open, click Tools on the menu bar, point to Protection, and then click Unprotect Sheet on the Protection submenu • When the Unprotect Sheet dialog box displays, type cartydollars as the password and then click the OK button

27. Adding a Command Button Control to the Worksheet • Right-click a toolbar at the top of the screen • When the shortcut menu displays, click Control Toolbox • Click the Command Button button on the Control Toolbox toolbar • Move the mouse pointer (a cross hair) to the upper-left corner of cell H5 • Drag the mouse pointer so the rectangle defining the button area appears as shown on the next slide and release the mouse button

28. Adding a Command Button Control to the Worksheet

29. Setting the Command Button Control Properties • With the Command Button control selected and Excel in Design mode, click the Properties button on the Control Toolbox toolbar • If necessary, when the Properties window displays, click the Alphabetic tab, click Caption, and then type New Forecast as the caption • Click ForeColor, click the ForeColor arrow, and then click the Palette tab • Click blue (row 3, column 7) on the ForeColor palette

30. Setting the Command Button Control Properties • 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

31. Setting the Command Button Control Properties

32. Entering the New Forecast Button Procedure Using the Visual Basic Editor • With the New Forecast button selected and Excel in Design mode, point to the View Code button on the Control Toolbox toolbar • Click the View Code button on the Control Toolbox toolbar • When the 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 • 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

33. Entering the New Forecast Button Procedure Using the Visual Basic Editor • Move the insertion point to the blank line and then type the seven comment statements (lines 1 through 7) in Table 7-4 on page EX 513. 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 • Enter lines 9 through 16 in Table 7-4. For clarity, indent all lines between the Sub statement and End Sub statement by three spaces

34. Entering the New Forecast Button Procedure Using the Visual Basic Editor • 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 Exit Design Mode button on the Control Toolbox toolbar • Click the Close button on the right side of the Control Toolbox toolbar title bar

35. Entering the New Forecast Button Procedure Using the Visual Basic Editor

36. Protecting a Worksheet and Saving the Workbook • Click Tools on the menu bar, point to Protection, and then click Protect Sheet on the Protection submenu • When the Protect Sheet dialog box displays, type cartydollars in the Password to unprotect sheet text box and then click the OK button. When the Confirm Password dialog box displays, type cartydollars and then click the OK button • Click File on the menu bar and then click Save As. When the Save As dialog box displays, type Carty Financial2 in the File name text box. Make sure 3½ Floppy (A:) displays in the Save in box and then click the Save button • Click the Close button on the right side of the menu bar to close the Carty Financial2 workbook

37. Adding Additional Controls and Setting the Properties • Using the instructions on pages EX 523 through EX 552, add the remaining controls to the worksheet and set their properties

38. Testing the Controls in the Personalization Center • Click the Enter Name and Salary button in the Personalization Center • When Excel displays the Enter dialog box with the prompt message, Client Name?, type Don McLeod as the client name • When Excel displays the Enter dialog box with the prompt message, Annual Salary?, type the negative number –40000 as the annual salary • When Excel displays the Enter dialog box with the prompt message, Annual salary must be > zero, type 72000 as the annual salary • Use the Client Investment scroll bar to change the value in cell D7 to 12%

39. Testing the Controls in the Personalization Center • Click the Include Employer Match check box if it does not have a check mark • Use the Employer Match scroll bar to change the value in cell D8 to 6% • Click the Annual Return spin button arrows to change the value in cell D9 to 4% • Click the Years of Service spin button arrows to change the value in cell D10 to 30

40. Testing the Controls in the Personalization Center

41. Reviewing a Digital Signature on a Workbook • Click Tools on the menu bar and then click Options. If necessary, when the Options dialog box appears, click the Security tab • Click the Digital Signatures button • Click a name in the Signer column of the Signatures box • When you are finished reviewing the certificate, click the OK button in the Digital Signatures dialog box and then click the OK button in the Options dialog box

42. Summary • Use the Undo button to undo multiple changes • 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

43. Summary • Customize a toolbar by adding a button • Customize a menu by adding a command • 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

44. Summary • Use VBA to prompt a worksheet user for input and display messages • Understand Do-While and If-Then-Else statements • Test and validate incoming data • Review a digital certificate on a workbook

45. Excel Project 7 Complete