Your Company, Inc

EXCEL INDEPENDENT PROJECT ON MACROS
This project is not found in your book. Use what you have learned so far in the course
and apply critical thinking skills to stretch the limits of your knowledge. Read the
assignment instructions carefully below or
from Excel_Independent_Project_Macros_Instructions.pdf. Compare your results to
the grading rubric before submitting.
This project requires you to explore more information on macros. Since most Excel users are not
programmers who know VBA code, this project gives you more practice on recording macros without
writing any VBA code. You will learn to record macros and show Excel what task you want the macro to
do. The code is automatically written for you! To learn how to record macros, begin by watching the
PowerPoint presentation below. This presentation contains audio on each slide. At the end of the
presentation, there are three demonstration videos that show how to record macros without using VBA
code. You must look at this PowerPoint presentation and watch the videos in order to be able to
complete the assignment.
Macros_Made_Easy.pptx - Click on the link to the left to see a PowerPoint presentation that includes
narration and links to 3 demonstration videos about macros.
To access the video links for the Demonstration Problem, you must RUN the slide show. Click on the
Slide Show icon in the lower right corner of your screen or click on Slide Show and From Beginning in
the ribbon at the top of your screen. (Note: Running the slide show is different than just scrolling
through the slides.) Press the Enter key to advance to the next slide.
If you do not have PowerPoint on your computer, you may download the PowerPoint viewer for free
at http://www.microsoft.com/en-us/download/details.aspx?id=13
After watching the presentation and the demonstration videos in the PowerPoint presentation, follow the
instructions below to complete your Independent Project assignment. This project requires you to apply
what you have learned without step by step instructions. This is similar to what you might encounter in
the workplace when you are asked to do a project for your boss!
You work in the office of Johnson's Appliance Centre. Part of your job is to keep track of all pending
sales orders and use this information to help salesmen get jobs on the Delivery Schedule as soon as all
items needed for the order are in stock. This Excel file is kept on the company server so all salesmen
(Rick, Doug, Steve and John) can access it. However, the salesmen are not fluent in Excel and have
trouble filtering the data to show just the orders that they need to schedule.
Open the IP_Macros_PendingSales.xlsx file.
To assist the salesmen, create a macro for each of the four salesmen (Rick, Doug, Steve and
John) that does the following:
 Filters the list for their name in the salesmen column
 Shows Yes in the All Items in Stock column
 Sorts the results by Delivery Zone.
After creating the macros, add a button near the top of the worksheet around row 3 for each salesman
so they can run the report.
Last, create a macro that resets the list to show all orders and add a button to run this macro in row 3
too.
Format the worksheet to print professionally. Add a footer with your name, the file name and the sheet
name.
Save the file as Lastname_Firstname_IP_Macros_PendingSales.xlsm. (Don't forget to change the file
type to Macro Enabled Workbook (*.xlsm) when you save.)
Grading Rubric for Independent Project on Macros:
Description Points
Macros recorded to display required results for each salesmen (Rick, Doug,
Steve and John) 40
Buttons added to run macros for each salesmen. 20
Macro recorded to reset list to show all records. 10
Button added to Show All records. 5
Buttons are labeled clearly and well organized (size, color, etc.) 10
Macros work regardless of which cell is selected to run. 5
Sheet is set to print. 5
Footer is added with name, filename and sheet name. 5
Total Points 100
After completing the assignment, attach the Lastname_Firstname_IP_Macros_PendingSales.xlsm file
and submit for grading.