1 / 52

Microsoft Excel 2010

Microsoft Excel 2010. Chapter 10 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

solana
Télécharger la présentation

Microsoft Excel 2010

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. MicrosoftExcel 2010 Chapter 10 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 • Customize the Quick Access Toolbar by adding a button • Understand Visual Basic for Applications (VBA) code and explain event-driven programs Using Macros and Visual Basic for Applications (VBA) with Excel

  3. Objectives • Add controls, such as command buttons, options buttons, and check boxes to a worksheet • Assign properties to controls • Use VBA to write a procedure to automate data entry into a worksheet • Review a digital signature on a workbook Using Macros and Visual Basic for Applications (VBA) with Excel

  4. Project – Green Valley Conservation Group Using Macros and Visual Basic for Applications (VBA) with Excel

  5. General Project Guidelines • Determine the actions you want to automate, and plan the steps to create a macro • Create the first user interface • Determine properties for controls in the user interface • Establish the Visual Basic code needed for each control • Create the second user interface • Evaluate the completed user interface through testing Using Macros and Visual Basic for Applications (VBA) with Excel

  6. Unprotecting a Password-Protected Worksheet • Click the Unprotect Sheet button (Review tab | Changes group) to display the Unprotect Sheet dialog box • Type the password in the Password text box • Click the OK button to unprotect the worksheet Using Macros and Visual Basic for Applications (VBA) with Excel

  7. Unprotecting a Password-Protected Worksheet Using Macros and Visual Basic for Applications (VBA) with Excel

  8. Displaying the Developer Tab, Enabling Macros, and Saving a Workbook as a Macro-Enabled Workbook • Click the File tab to display the Backstage view • Click the Optionscommand to display the Excel Options dialog box • Click the Customize Ribbon tab to display the Customize Ribbon tools • Click the Developer check box in the Main Tabs list to select the Developer tab for display on the Ribbon Using Macros and Visual Basic for Applications (VBA) with Excel

  9. Displaying the Developer Tab, Enabling Macros, and Saving a Workbook as a Macro-Enabled Workbook • Click the OK button to display the Developer tab on the Ribbon • Click the Macro Security button (Developer tab | Code group) to display the Trust Center dialog box • Click the ‘Enable all macros’ option button to select it • Click the OK button to close the dialog box and enable macros Using Macros and Visual Basic for Applications (VBA) with Excel

  10. Displaying the Developer Tab, Enabling Macros, and Saving a Workbook as a Macro-Enabled Workbook • Click the File tab to display the Backstage view • Click the Save As command to display the Save As dialog box • When the Save Asdialog box is displayed, click the ‘Save as type’ button to display a list of file types • Click Excel Macro-Enabled Workbook in the ‘Save as type’ list to select the appropriate file type • Click the Save button to save the workbook as an Excel Macro-Enabled Workbook file Using Macros and Visual Basic for Applications (VBA) with Excel

  11. Displaying the Developer Tab, Enabling Macros, and Saving a Workbook as a Macro-Enabled Workbook Using Macros and Visual Basic for Applications (VBA) with Excel

  12. Displaying the Developer Tab, Enabling Macros, and Saving a Workbook as a Macro-Enabled Workbook Using Macros and Visual Basic for Applications (VBA) with Excel

  13. Converting Names to Proper Case Using Macros and Visual Basic for Applications (VBA) with Excel

  14. Recording a Macro • Click the Use Relative References button (Developer tab | Code group) so that the macro uses relative cell references when selecting cells • Click the Record Macro button (Developer tab | Code group) to display the Record Macro dialog box • Enter the desired name and shortcut key, select the desired store location, and enter the desired description • Click the OK button to begin recording the macro and change the Record Macro button to the Stop Recording button • Perform the desired actions to add to the macro • Click the Stop Recording button (Developer tab | Code group) to stop recording the worksheet activities Using Macros and Visual Basic for Applications (VBA) with Excel

  15. Recording a Macro Using Macros and Visual Basic for Applications (VBA) with Excel

  16. Setting the Macro Security Level to Medium • Click the Macro Security button (Developer tab | Code group) to display the Trust Center dialog box • Click the ‘Disableall macros with notification’ option button • Click the OK button to close the dialog box Using Macros and Visual Basic for Applications (VBA) with Excel

  17. Setting the Macro Security Level to Medium Using Macros and Visual Basic for Applications (VBA) with Excel

  18. Opening a Workbook with Macros and Executing a Macro • Open the desired workbook containing a macro • Click the Enable Content button in the Security Warning bar to open the workbook with macros enabled • Click the desired macro’s shortcut key to run the macro Using Macros and Visual Basic for Applications (VBA) with Excel

  19. Viewing and Printing a Macro’s VBA Code • Click the Macros button (Developer tab | Code group) to display the Macro dialog box • When the Macro dialog box is displayed, click the name of the macro to view and print • Click the Edit button to display the Microsoft Visual Basic Editor • Click File on the menu bar to display the File menu • Click the Print command to display the Print dialog box • 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 to close the Visual Basic Editor window Using Macros and Visual Basic for Applications (VBA) with Excel

  20. Viewing and Printing a Macro’s VBA Code Using Macros and Visual Basic for Applications (VBA) with Excel

  21. 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 to display a list of commands to add to the Quick Access Toolbar • Click Macros in the ‘Choose commands from’ list to display a list of macros • Click the desired macro to add Using Macros and Visual Basic for Applications (VBA) with Excel

  22. Adding a Button to the Quick Access Toolbar, Assigning the Button a Macro, and Using the Button • Click the Add button to add the macro to the Customize Quick Access Toolbar list • Click the Modify button to display the Modify Button dialog box • Click the desired icon in the Symbol list to select it • Click the OK button to assign the icon to the new command and to close the dialog box • Click the OK button to close the dialog box • Click the new button on the Quick Access Toolbar to run the macro Using Macros and Visual Basic for Applications (VBA) with Excel

  23. Adding a Button to the Quick Access Toolbar, Assigning the Button a Macro, and Using the Button Using Macros and Visual Basic for Applications (VBA) with Excel

  24. Adding a Command Button Control to the Worksheet • Click the Insert button (Developer tab | Controls group) to display the Controls gallery • Click the Command Button button in the ActiveX Controls 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 desired location for the command button • Drag the mouse pointer to draw the command button • Release the mouse button to add the Command Button control with the default caption CommandButton1 Using Macros and Visual Basic for Applications (VBA) with Excel

  25. Adding a Command Button Control to the Worksheet Using Macros and Visual Basic for Applications (VBA) with Excel

  26. Setting the Command Button Control Properties • With the Command Button control selected and Excel in Design mode, click the Properties button (Developer tab | Controls group) to open the Properties window for the Command Button control • If necessary, when theProperties window opens, click the Alphabetic tab • Click Caption in the Properties list, double-click the entry in the Caption box, and then type the desired caption • Click ForeColor in the Properties list, click the ForeColor arrow, and then click the Palette tab to display a palette of colors to apply to the button text Using Macros and Visual Basic for Applications (VBA) with Excel

  27. Setting the Command Button Control Properties • Select the desired color • Click Font in the Properties list and then click the ellipsis button to display the Font dialog box • When the Font dialog box appears, click the desired font, font style, and size • Click the OK button to display the Command Button control Using Macros and Visual Basic for Applications (VBA) with Excel

  28. Setting the Command Button Control Properties Using Macros and Visual Basic for Applications (VBA) with Excel

  29. Entering the New Data Button Procedure Using the Visual Basic Editor • Double-click the button to display 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 of the Microsoft Visual Basic window to maximize it • 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 • Position the insertion point on a blank line between the Sub and End Sub statements Using Macros and Visual Basic for Applications (VBA) with Excel

  30. Entering the New Data Button Procedure Using the Visual Basic Editor • Enter the desired code • Click the Close button on the right side of the Microsoft Visual Basic title bar to return to the worksheet • Click the Close buttonon the right side of the Properties window to close the window • Click the Design Mode button to return to Run mode Using Macros and Visual Basic for Applications (VBA) with Excel

  31. Entering the New Data Button Procedure Using the Visual Basic Editor Using Macros and Visual Basic for Applications (VBA) with Excel

  32. Adding Form Controls to a User Interface • Click the Insert button (Developer tab | Controls group) to display the Controls gallery • Click the Option Button button in the Form Controls area in the Controls gallery • Drag the mouse pointer to the desired location • Click the Insert button (Developer tab | Controls group) to display the Controls gallery • Click the Check Box button in the Form Controls area in the Controls gallery • Drag the mouse pointer to the desired location Using Macros and Visual Basic for Applications (VBA) with Excel

  33. Adding Form Controls to a User Interface Using Macros and Visual Basic for Applications (VBA) with Excel

  34. Grouping Option Buttons in a User Interface • Click the Insert button (Developer tab | Controls group) to display the Controls gallery • Click the Group Box button in the Form Controls area in the Controls gallery • Drag the mouse pointer so the Group Box control is in the desired location Using Macros and Visual Basic for Applications (VBA) with Excel

  35. Grouping Option Buttons in a User Interface Using Macros and Visual Basic for Applications (VBA) with Excel

  36. Formatting the Option Button Controls • With Excel in Design mode, click the option button control to format • Use the sizing handles to resize the option button • Right-click the control to display the shortcut menu • Click Edit Text on the shortcut menu so you can edit the control text • Delete the text in the control and type the replacement text • Select the control(s) to resize • Click the Align button (Drawing Tools Format tab | Arrange group) to display the alignment options • Click the desired alignment option Using Macros and Visual Basic for Applications (VBA) with Excel

  37. Formatting the Option Button Controls Using Macros and Visual Basic for Applications (VBA) with Excel

  38. Recording User Input to Another Location on the Worksheet • Right-click the control to link to display the shortcut menu • Click Format Control to display the Format Control dialog box • If necessary, click the Control tab to display the Control settings • Enter the desired cell in the Cell link box to enter the identity of the control in that cell • Click the OK button to close the dialog box Using Macros and Visual Basic for Applications (VBA) with Excel

  39. Recording User Input to Another Location on the Worksheet Using Macros and Visual Basic for Applications (VBA) with Excel

  40. Assigning Names to Cells • Select the cell to name • Click the Name Manager button (Formulas tab | Defined Names group) to open the Name Manager dialog box • Click the New button to open the New Name dialog box • Enter the desired name • Click the OK button to enter the cell name • Click the Close button to close the dialog box Using Macros and Visual Basic for Applications (VBA) with Excel

  41. Assigning Names to Cells Using Macros and Visual Basic for Applications (VBA) with Excel

  42. Assigning Names to Cells Using Macros and Visual Basic for Applications (VBA) with Excel

  43. Entering the Command Button Procedures Using the Visual Basic Editor • With the command button selected and Excel in Design mode, click the View Code button (Developer tab | Controls group) to display the Microsoft Visual Basic Editor • Click the Object box arrow at the top of the window and then click the name of the command button • Enter the desired VBA code Using Macros and Visual Basic for Applications (VBA) with Excel

  44. Entering the Command Button Procedures Using the Visual Basic Editor Using Macros and Visual Basic for Applications (VBA) with Excel

  45. Removing the Outline from the Group Control • Switch to the Visual Basic Editor • Press the CTRL+G keys to open the Immediate window • Type activesheet.groupboxes.visible= falseand press ENTER to remove the box from around the group control Using Macros and Visual Basic for Applications (VBA) with Excel

  46. Removing the Outline from the Group Control Using Macros and Visual Basic for Applications (VBA) with Excel

  47. Preparing and Protecting the Worksheet and Saving the Worksheet • Click File on the Ribbon to open the Backstage view • Click Options todisplay the Excel Options dialog box • Click Advanced in theleft pane to display the advanced options • Click the Show pagebreaks and ‘Show a zero in cells that have zero value’ check boxes to deselect them • Click the OK button to close the dialog box • Click the Gridlines, Formula Bar, and Headings check boxes (View tab | Show group) to deselect them • Click the Minimize the Ribbon button on the title bar to minimize the Ribbon Using Macros and Visual Basic for Applications (VBA) with Excel

  48. Preparing and Protecting the Worksheet and Saving the Worksheet • Click the Protect Sheet button (Review tab | Changes group) to display the Protect Sheet dialog box • Type the desired password in the ‘Password to unprotect sheet’ text box • Verify the password when prompted • Click the OK button to close the dialog box • Click the Save button on the Quick Access Toolbar to save the workbook Using Macros and Visual Basic for Applications (VBA) with Excel

  49. Preparing and Protecting the Worksheet and Saving the Worksheet Using Macros and Visual Basic for Applications (VBA) with Excel

  50. Chapter Summary • 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 • Customize the Quick Access Toolbar by adding a button • Understand Visual Basic for Applications (VBA) code and explain event-driven programs Using Macros and Visual Basic for Applications (VBA) with Excel

More Related