Part 1 (10 pts): Create a Macro using the Macro recorder and Visual Basic statements that will do the following: 1. Create a macro recording the advanced Filter for just those employees located in Chicago Create a form button for this macro and call it Chicago assign the short cut key Ctrl + Shift + c to the macro Add comments describing what this macro does in the VB window 2. Create a macro recording the advanced Filter for just those employees located in Atlanta Create a form button for this macro and call it Atlanta Assign the short cut key Ctrl + Shift + A to the macro Add comments describing what this macro does in the VB window 3. Create a macro recording the advanced Fater for ALL employees Create a form button for this macro and call it All Employees Assign the short cut key Ctrl + Shift + L to the macro Add comments describing what this macro does in the VB window 4. Create 2 macros: one that applies conditional formatting to those individuals who are managers and one that clears the conditional formatting Create 2 form buttons for the macros and call them Managers and ALL respectively Assign the short cut key Ctrl + Shift + M and Ctrl + Shift + K to the macros D E F G B 1 Name Location Title 2 Adams Atlanta Trainee 3 Adamson Chicago Manager 4 Brown Atlanta Trainee 5 Charles Boston Account Re 6 Coulter Atlanta Manager 7 Elofson Miami Account Re 8 Gillenson Miami Manager 9 James Chicago Account Re 10 Johnson Chicago Account Re 11 Manin Boston Account Re 12 Marder Chicago Account Re 13 Milgrom Boston Manager 14 Rubin Boston Account Re 15 Smith Atlanta Account Re 16 17 Name Location Title Service Hire Date Salary 27.4 11/24/93 $19,500 29.1 3/16/92 $52,000 27.4 11/24/93 $18,500 29.1 3/16/92 $40,000 27.4 11/24/93 $100,000 28.4 10/31/92 $47,500 27.4 10/31/93 $55,000 31.4 10/31/89 $42,500 30.4 10/31/90 $47,500 29.1 3/16/92 $49,500 29.4 10/31/91 $38,500 29.1 3/16/92 $57,500 29.1 3/16/92 $45,000 27.4 11/24/93 $65,000 Service Hire Date Salary 18 Summary Statistics 19 20 21 22 Average Salary 23 Maximum Salary 24 Minimum Salary 25 Total Salary 26 Number of Employees 27 28 $48,429 $100,000 $18,500 $678,000 14 Part 1 (10 pts): Create a Macro using the Macro recorder and Visual Basic statements that will do the following: 1. Create a macro recording the advanced Filter for just those employees located in Chicago Create a form button for this macro and call it Chicago assign the short cut key Ctrl + Shift + c to the macro Add comments describing what this macro does in the VB window 2. Create a macro recording the advanced Filter for just those employees located in Atlanta Create a form button for this macro and call it Atlanta Assign the short cut key Ctrl + Shift + A to the macro Add comments describing what this macro does in the VB window 3. Create a macro recording the advanced Fater for ALL employees Create a form button for this macro and call it All Employees Assign the short cut key Ctrl + Shift + L to the macro Add comments describing what this macro does in the VB window 4. Create 2 macros: one that applies conditional formatting to those individuals who are managers and one that clears the conditional formatting Create 2 form buttons for the macros and call them Managers and ALL respectively Assign the short cut key Ctrl + Shift + M and Ctrl + Shift + K to the macros D E F G B 1 Name Location Title 2 Adams Atlanta Trainee 3 Adamson Chicago Manager 4 Brown Atlanta Trainee 5 Charles Boston Account Re 6 Coulter Atlanta Manager 7 Elofson Miami Account Re 8 Gillenson Miami Manager 9 James Chicago Account Re 10 Johnson Chicago Account Re 11 Manin Boston Account Re 12 Marder Chicago Account Re 13 Milgrom Boston Manager 14 Rubin Boston Account Re 15 Smith Atlanta Account Re 16 17 Name Location Title Service Hire Date Salary 27.4 11/24/93 $19,500 29.1 3/16/92 $52,000 27.4 11/24/93 $18,500 29.1 3/16/92 $40,000 27.4 11/24/93 $100,000 28.4 10/31/92 $47,500 27.4 10/31/93 $55,000 31.4 10/31/89 $42,500 30.4 10/31/90 $47,500 29.1 3/16/92 $49,500 29.4 10/31/91 $38,500 29.1 3/16/92 $57,500 29.1 3/16/92 $45,000 27.4 11/24/93 $65,000 Service Hire Date Salary 18 Summary Statistics 19 20 21 22 Average Salary 23 Maximum Salary 24 Minimum Salary 25 Total Salary 26 Number of Employees 27 28 $48,429 $100,000 $18,500 $678,000 14