1 / 16

Computer Science & Engineering 2111

Computer Science & Engineering 2111. CSE 2111 Lecture Data Validation, Worksheet Protection, and Macros. Data Validation. Controls the type of data or the values input in a cell Reduces input errors Multiple criterion can be applied to a cell. Macro.

donagh
Download Presentation

Computer Science & Engineering 2111

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. Computer Science & Engineering 2111 CSE 2111 Lecture Data Validation, Worksheet Protection, and Macros CSE 2111 Lecture-Data Validation and Macros

  2. Data Validation • Controls the type of data or the values input in a cell • Reduces input errors • Multiple criterion can be applied to a cell CSE 2111 Lecture-Data Validation and Macros

  3. Macro A series of commands and functions that are stored in a Microsoft Visual Basic module • Automates your keystrokes and actions in Excel • Use these when you complete the same tasks repetitively • When you create a macro VBA program code is automatically generated CSE 2111 Lecture-Data Validation and Macros

  4. Macro Example Scenario You work for a company that employs people to make customer satisfaction calls for various companies. Your department provides services for all the major auto makers in the U.S. Each employee is assigned a state and it is their job to call each household and conduct a brief survey. You receive a weekly list of customers from each auto company and it is your job to separate the list by state and distribute the list to your employees. Your manager would like the customers stored in an Excel file with each state having its own worksheet. CSE 2111 Lecture-Data Validation and Macros

  5. File Sent from Vendor Comma Delimited File, meaning each piece of information is separated by a comma. (Created in Notepad or another text editor.) • Information can be separated by other characters such as a colon, or tab. • Information can be sent in different formats such as .csv or xml files. CSE 2111 Lecture-Data Validation and Macros

  6. Create your Template • Create an Excel Workbook named Customers. • Create tabs with the names of the States you need. • Input information that will not change • Copy the worksheet created to all the tabs and change the state name (Follow the slides to complete steps 4-6) CSE 2111 Lecture-Data Validation and Macros

  7. Create data validation rules for your spreadsheet CSE 2111 Lecture-Data Validation and Macros

  8. Break Down into Separate Macros • Macro Name: UnprotectWorksheets • Macro Name: ImportFile • Delete contents from every worksheet • Import information • Macro Name: SeparateStates • Concatenate phone number • Filter • Copy/Paste • Macro Name: ProtectWorksheets Note: Always create a backup first CSE 2111 Lecture-Data Validation and Macros

  9. ProtectWorksheet Macro CSE 2111 Lecture-Data Validation and Macros

  10. CSE 2111 Lecture-Data Validation and Macros

  11. ImportFile Macro CSE 2111 Lecture-Data Validation and Macros

  12. SeparateStates Macro CSE 2111 Lecture-Data Validation and Macros

  13. CSE 2111 Lecture-Data Validation and Macros

  14. Example of VBA Program Code generated from recording macro Sub ImportFile() Range("A4:H56").Select Selection.QueryTable.Delete Selection.ClearContents Range("A4").Select With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;L:\CSE2111\Autumn 2013\CSE 2111 Slides AU 2013\Lecture 9-Data Validation and Macros\Macro Test\Customers.txt" _ Step through a macro CSE 2111 Lecture-Data Validation and Macros

  15. Assigning Macro to the Quick Access Toolbar • FileOptionsQuick Access Toolbar CSE 2111 Lecture-Data Validation and Macros

  16. CSE 2111 Lecture-Data Validation and Macros

More Related