HW#24 - 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 #23 so that it traps for worksheet events.  After completing HW 23, 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 Resize Table button and Update Chart 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. If the cell was B4, then display a warning message using msgbox telling them that resizing the table will delete the data in the table. If they click the OK button, go ahead and resize the table. If they click Cancel, do nothing. 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
    display msbox with warning message and OK-Cancel buttons
    if the user clicks OK on the warning message
        resize the table
else  <something other than B4 was changed>
    update the chart

Note that you will have an IF statement nested within the IF clause of your outer IF statement.

Trap on the worksheet change event so that the chart is automatically updated (just call your existing subroutine) whenever something on the worksheet changes (the second part of the if statement). 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 (but you don't have to):

Dim currange As Range
Set currange = Application.Selection

<put your code here to update the chart>


Test your code to make sure it works.

Submittal Instructions:

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

2. Upload your spreadsheet via Learning Suite.