CE En 270 - Homework #21

Working with Events

For this assignment, I would like you to modify the spreadsheet involving a parts list and a pie chart that you built for HW #20 so that it traps for worksheet events.  After completing HW 16, your spreadsheet should look something like this:

We will make this spreadsheet a little more automated by doing the following:

Part A - Workbook Events

Trap on the Open event for the workbook so that it displays a message using MsgBox when the user first opens the spreadsheet.  I don't care what message you display (be creative!).

Part B - Worksheet Events

Make the following changes to your spreadsheet to trap on worksheet events:

1) Delete the Update Chart button.  Then trap on the worksheet change event so that the chart is automatically updated (just call your existing subroutine) whenever something on the worksheet changes.  Note that when you update the chart, your macro may mess up your selection.  It is a good idea to find out what the selection is prior to executing your code and then restore the original selection when you are done.  You can do that as follows:

Dim currange As Range
Set currange = Application.Selection

<put your code here to update the chart>

currange.Select

Test your code to make sure it works.

2) Delete the Resize Table button. Then modify the worksheet change event so that whenever the worksheet is changed, it checks to see if the range that was changed (the "Target") is cell B4.  To do this, check to see if Target has the same address as B4 using the methodology described at the bottom of the Events section of the VB Excel Primer.  Then, if the cell changed is something other than cell B4, call the code to update the plot.  In summary, your code should be organized as follows (in pseudo-code):

if the target is cell B4 then

    if the user clicks OK on the warning message

        resize the table

    else

        do nothing

else  <something other than B4 was changed>

    update the chart

Test your code to make sure it works.

Submittal Instructions:

Click here to upload your homework assignment.