VBA can be used to make interactive charts that respond to mouse movements. In this article we first start of by making a simple bar chart embedded in a chart sheet and then use the mouse move event of the chart sheet to make it interactive.

Create a simple Bar Chart

For the purpose of this exercise, we take the list of top 15 billionaires and sort it based on their networth.

We then create a simple bar chart using the networth as the pot values and the names as X-Axis lables. The last step in the creation of this bar chart is when excel prompts you for the location at which the new chart is to be placed. We select the chart to be placed in a new sheet.

The application will then place a special sheet which acts as a placeholder for the chart. The important thing to nore here is that this sheet (called the “chart sheet”) exposes certain functions such as the mouse move, mousedown, mouseup etc) that the generally not available to a chart embedded in a normal excel worksheet.

Once the chart has been created, we remove all the chartjunk and provide proper labels.

Adding VBA code to make the Chart Interactive

A chart sheet exposes the mousemove event which can be exploited to know the position of the pointer at any given point in time. The coordinates of the mouse pointer are provided inherently as X and Y variables. We then pass on these variables to the chart’s GetChartElement function. This function helps provide details of what chart elements exist at a given X and Y coordinate. The syntax of the GetChartElement function is as follows:

ChartObject.GetChartElement(X, Y, ElementID, Arg1, Arg2)

where :
ChartObject refers to the chart whose
X The X coordinate of the chart element.
Y The Y coordinate of the chart element.
ElementID This argument returns the XLChartItem value of the chart element at the specified coordinate.
Arg1 and Arg2 Additional arguments that provide details related to the chart object

(The me keyword serves pretty much the function as this in java – it helps get a handle on the current object.)

Please bear in mind that ElementID, Arg1 and Arg2 are passed byRef which means that their values get changes as a result of being specified as arguments to the method. So in essence, we provide only the first two arguments with the remaining three being returned with fresh values by the method.

Here is screenshot from MS VBA help file that shows that those return values for the ElementID, Arg1 and Arg2 indicate.

We now check the ElementID to see if the underlying element is an xlSeries – indicating that the mouse is over an data plot series – a bar in a bar chart or a line in a line chart. Once we know that the underlying element is a data plot, all that needs to be done to make the chart interactive is the fill in the code with the steps we would like to take. In our case, we simply provide the mouse pointer with a tool tip that comes up when the mouse moves over a bar and disappears when it moves out of the bar. While at it, we add another effect – which is to make that particular bar “light-up” when the mouse moves over it.

The final effect is that whenever the mouse moves over a bar in our interactive chart, a tool-tip appears with customized description and the bar changes color.

The code to achieve this entire effect has been give below. The original code developed by Jon Peltier can be found here.

You can download a sample worksheet with the interactive chart using VBA mouse move event here or click on the button below: