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:
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!).
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>
currange.Select
Test your code to make sure it works.
1. Be sure to save the changes to your spreadsheet.
2. Upload your spreadsheet via Learning Suite.