In this workout we are required to write VBA code to enable chart events with the chart below so that when a data series point is mouse clicked, the breakdown of that company's net income is displayed.

We can see that in the chart.

We have three data points for company A, company B, and company C. Company A's net income of 98 is given further detail in the company.

A data sheet Company B's net income is broken down further on the Company B sheet and of course Company C's net income of 75 is broken down further on the Company C sheet.

And we want to enable chart events so that when the appropriate data series is selected we get the relevant sheet appear.

Let's jump into the visual basic editor and write our code.

Our first task is to insert a new class module.

Let's make the properties appear and we are going to rename this class module as Emb chart class.

We then need to declare a public chart object variable and we'll call this my chart class.

We use the word public and then the key word with events.

The with events keyword is used so that the chart object variable that represents our chart will respond to events public with events.

My chart class as chart.

Next we want to insert a module and within this module we must connect the declared object in the class module with our embedded chart.

In order for the event procedure to run in our new module we'll declare a new variable.

Let's call this one summary chart as an object type of Emb chart class, the name of our class module.

We need to make this a module level object variable.

We declare using dim Summary chart as the new Emb chart class.

Next we are required to write code to associate the summary chart object with the embedded chart on our worksheet.

Now we may not always want chart events to trigger.

We can include a checkbox on the worksheet and write code to turn events on and off.

Let's go back into the front end of Excel and insert a checkbox back on the front end of Excel.

We go developer insert and we'll insert a form control checkbox.

We should note that this checkbox is named Checkbox 1.

and our chart is named Chart 8.

back into the visual basic editor and our objective here is to create a chart that can be interacted with if a series object is left mouse clicked.

If a chart is left clicked, the default is for Excel to highlight the chart and the dataset and we require code to change this default behavior.

First let's identify the check box in our interactive chart worksheet.

We're going to set up a new procedure and we'll call this one Checkbox Click. If worksheets interactive chart and from the checkbox collection we identify the checkbox named Checkbox 1.

Check if checkbox one equals Xlon ie, the checkbox is checked.

Then what do we want to do? We want to set the summary chart.my chart class object to be the chart on the worksheet.

And remember that one was named Chart 8.

We use set summary chart my chart class to be equal to worksheets interactive chart and from the chart objects collection we specify Chart 8 and of course we use the chart method to identify the chart object itself.

Otherwise, what should we do? We use the Else syntax and we specify if the checkbox is off.

Then we want to disable chart events.

We're going to use the nothing keyword to disassociate the object variable from the actual object.

So else set summary chart.my chart class to be equal to nothing.

We can now end our if and we go to the next stage.

Our next and final stage is to select the new object in the object box.

We are looking for the my chart class object and the desired object event.

So let's select the new object of MyChart class and our event is going to be mouse down ie, a left mouse click.

Now we don't need the activate event so I'll delete that one and I'm going to write code that is triggered by the mouse down event.

When we click with the left mouse button, the get chart element method is used to determine what element of the chart was clicked and the get chart element method returns the following information.

It returns X and Y, the coordinates for the position of the chart that was clicked.

It returns the element ID num.

Now that's the element of the chart that was selected.

It returns A which relates to the data series number and it returns a variable B, which relates to the data series point.

Now in order for our code to run, we need to declare a number of variables.

We're going to declare ID num as long, we're going to declare A As long and we need to declare B as a long variable type my chart plus get chart element method and we want to get the X coordinate the Y coordinate that was clicked, the id, num, ie, which element of the chart was clicked, the A, which relates to the data series number that was selected.

And finally B, which relates to the data series point should a data series have been clicked.

Now if a data series element of the chart is clicked, then we want our procedure to run and we can write if Id num ie, the element of the chart that was selected is equal to an xl series.

Then what do we want to do? We can write some select case code to perform a number of different instructions given our variable B and here variable B is the data series point.

We write select case B, and in the case where B is 1 ie, this is the first point of the data series and what do we want to do? We want to specify worksheets, company A and the select method ie. If that first point ie, company A is selected, then we want to choose the company A sheet case two, where B equals 2. That would mean that the second data series point has been selected and we want to show company B.

We say worksheets, company b.select.

Our final case will be the case where B equals 3.

The data series 0.3 is selected and that data series 0.3 relates to company C and we want to select the company C sheet Worksheets, company C select.

That ends our selects case and it ends our if, let's go back to the front end of Excel, run our macro and test. If our code works back in the front end of Excel, the caption in our checkbox isn't terribly intuitive.

Checkbox one isn't very helpful to us.

Let's change that caption to enable chart events right. Click on the checkbox and we can change our caption.

Let's ensure that design mode is off.

We want to a run the macro that we've written.

View macros, check box, click.

Let's run this one and to test, if we click onto one point in the data series, let's test our interactive chart.

We check the enable chart events box, click on the first data series point and company A is shown.

Let's click on the third data series point for company C and the company C sheet is displayed.