Working with worksheet events and VBA

For the reporting scenario that was outlined in the Introduction to this chapter, the intent is to allow the user to click on a pivot table row item and then have this action create downstream activity affecting other elements on the worksheet. That is, when the user clicks on a category item in the pivot table (the pivot titled sales_by_cat_all), the bar chart showing year-on-year category sales will change to show the selected category.

This type of action may be achieved through various methods—the use of a Filter or Slicer connecting the two pivot tables immediately springs to mind. However, in doing this, the end user is still required to select something from the drop down (Filter) or Slicer. We want to interrogate the clicking action on a part of the spreadsheet to facilitate a change. Furthermore, while the event (recipe) applies to a pivot table, there is no restriction to binding this action to a pivot table. The technique can be applied to...