HW#23 - Updating Charts

For this assignment you will make some enhancements to the spreadsheet you developed for HW #22.  When you are finished, your spreadsheet will look something like this:

You will add a pie chart to your spreadsheet that shows the breakdown of the part costs. When you first create the chart, it will be tied to the filled-in portion of your table (A7:B11 in the example shown above). However, when you add a new part to the list, the chart will not automatically include it. Unfortunately, you cannot simply make the chart include the entire active portion of the table (A7:B24). The empty cells mess up the display of the chart. Fortunately, we can solve this problem easily with VB. We will also add a warning message using MsgBox whenever the user attempts to resize the table.

Do the following:

1. Create your pie chart.

2. Record a macro that modifies the range of cells associated with the chart. To do this, right-click on the pie chart, select the Select Data command, and edit the row value at the end of the Chart data range address.

3. Modify the macro recorded in the previous step so that it takes a single argument as a parameter representing the number of items in the list.  The sub should then update the source data range to include all of the items currently in the table.

4. Add an Update Chart button that calls the sub you modified in the previous step. Note that when you call the sub you need to tell it how many items are in the list (by passing an argument). The best way to do this is to look at the value in the Total Parts cell. The location of this cell will actually change depending on the table size, so you need to use the number in the Table Size cell to make sure you get the total number of parts from the correct cell (B27 in the example shown above). Test your Update Chart button to make sure it works under all appropriate conditions (different table sizes and different numbers of parts).

5. Modify the code for the Resize Table button so that it prompts the user with an OK-Cancel MsgBox and only resizes the table if the user selects OK after being warned that he/she is about to lose all information in the table.

Submittal Instructions:

1. Be sure to save the changes to your spreadsheet.

2. Upload your spreadsheet via Learning Suite.