Interactive Chart in VBA using Mouse Move Event

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.

Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim ElementID As Long
Dim Arg1 As Long
Dim Arg2 As Long
Dim chart_data As Variant
Dim chart_label As Variant
Dim last_bar As Long
Dim chrt As Chart
Dim ser As Series

On Error Resume Next

Me.GetChartElement x, y, ElementID, Arg1, Arg2

Set chrt = ActiveChart
Set ser = ActiveChart.SeriesCollection(1)
chart_data = ser.Values
chart_label = ser.XValues

Set txtbox = ActiveSheet.Shapes("hover")

If ElementID = xlSeries Then
    If Err.Number Then
        Set txtbox = ActiveSheet.Shapes.AddTextbox _
                                        (msoTextOrientationHorizontal, x - 150, y - 150, 100, 100)
        txtbox.Name = "hover"
        txtbox.Fill.ForeColor.SchemeColor = 9
        txtbox.Line.DashStyle = msoLineSolid
        chrt.Shapes("hover").TextFrame.Characters.Text = "$ " & Application.WorksheetFunction.Text(chart_data(Arg2), "???.??") & " bn" & Chr(10) & Chr(10) & chart_label(Arg2)
        With chrt.Shapes("hover").TextFrame.Characters.Font
            .Name = "Arial"
            .Size = 12
            .ColorIndex = 16
        End With
        With chrt.Shapes("hover").TextFrame.Characters(Start:=1, Length:=11).Font
            .Name = "Haettenschweiler"
            .Size = 20
            .ColorIndex = 1
        End With
        last_bar = Arg2
    End If
    ser.Points(Arg2).Interior.ColorIndex = 44
    txtbox.Left = x - 150
    txtbox.Top = y - 150
    ser.Interior.ColorIndex = 16
End If
End Sub

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

interactive chart using VBA mouse move event

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. Amith wrote:

    Hi, do you have any macro which exports and imports custom fields in outlook calendar



  2. Ajay wrote:

    Hi Amith,

    I don’t but I think JP’s site ( would be a good place to start.


  3. Marco wrote:

    Hi your routine is functioning only if it is contained “into” chart.
    I need to have a similar routine functioning for all chart in a sheet.

    Can you please help me in creating such a routine?

    p.s. sorry for my english…

  4. Ajay wrote:

    That may be a bit difficult to achieve. But if you don’t mind a bit of coding, here is something that can track mouse movement in a sheet. You will have to paste the chart as an image and though it will make the chart image interactive, you will end up having an image rather than a chart. That may not be very useful if you want to use the use the specific chart attributes such as label etc.

    Welcome to da TaB !

  5. Jon Peltier wrote:

    The only thing I’d do differently is use a horizontal bar chart, so the labels with the billionaires’ names are more easily read.

  6. Chris Condley wrote:

    I’m trying to utilize this in Excel 2010 but apparently “interactive” charts” are a thing of the past. I changed your example spreadsheet to a scatter chart (that’s what I’m working with) and your code still worked. It doesn’t in my worksheet though, because I’m using a normal sheet, can’t seem to find a way to insert a “chart” sheet. Help?

  7. Bob Davis wrote:

    Excellent example. Very concise, very well written.

    I wish I had found your web site several days ago. It would have saved me several dead-end tries.

  8. Tj wrote:

    Do you not have an issue with the X/Y being chart coordinates vs aligning the shapes .Top/.Left screen/application coordinates?

    If not, why? Thanks!

  9. Tj wrote:

    Ah, nevermind… I see that it is a chartsheet.

  10. Pete wrote:

    Hi, i used your sheet and its been very helpful. One thing though, is there are way to pass an additional comments in the bars, so i want to have a line of text appear instead of the chart_label or chart_data

  11. Rachael wrote:

    Hi there

    I’d love it if you could help me use this for my own data. My labels are in A2:A13 on Sheet3…I can’t see how to reference them.

    Many thanks in anticipation


  12. Hannah wrote:

    Hello, thank you for this code, it works pretty well!
    Have you tried implementing this code with a graph that has been embedded into a worksheet? I know you said worksheets dont support Mouse Move, but what about in later versions like 2007 or 2010?

    Thank you!

  13. Arnie wrote:


    I am trying to highlight a certain pointer marker in a different colour as you have done on the mouse over.

    However, I need it to stay that colour and copy the graph, and get the copied graph to highlight the next marker point and so forth for the next 100 or so graphs, (all whilst returning to the main series colour ie. only one marker is a different colour per graph). Any help would be great.

  14. Andrew Martinelli wrote:

    Hi, just came across this excellent piece of code, really improves the UI for Excel Charts. I have a couple of questions on how (if) this code can be improved / tailored for slightly different applications:

    1. Can the mouseover label be populated with data other than the x or y values? Specifically, if my x-axis contains data from Column A and my y-axis is populated with data from Column B, can the labels be populated with data from Column C?

    2. Is there a simple way of applying formatting changes to the data point marker during the mouse over? say just increase the size of the marker and reset it when it is no longer moused over.

    Also, I have manged to fit the code provided on this page ( to a sample sheet, but given the high density of the points in my chart (I am using Scatter Plot) it seems the data in the text box is not updating. So I get say (1,1.5) as values in the text box for points close to each other, unless I physically mouse over away from the chart and bring the mouse back over the chart. It’s as though the close proximity of the data points is impeding a ‘refresh’ or re-evaluation of the data. I can send over a sample workbook if this helps.

    Using Excel 2010 and an .xlsm workbook.

    Thanks in advance!

  15. RK Chuahan wrote:

    very useful, I could find a solution for my days queries

  16. Paul wrote:


    do you think is it possible to create a similar effect for a chart like an object ? Can you share with me a solution ?

  1. How to create mouse-interactive ChartObject? wrote:

    […] Re: How to create mouse-interactive ChartObject? What are you going to be clicking and dragging? Only existing points on the chart? Depending on your answer to that we could be looking at using GetChartElement, and if it's a series point we can discover that point's coordinates, but when it come to where you're dragging to, if it's empty space on the chart's plot area then you may be looking at converting the pixel location (client coordinates) to the chart's XY values by maybe using the PlotArea's InsideTop, InsideLeft, InsideHeight and InsideWidth properties, comparing with the max and min x and y scales on the axis. So you've managed to get a new chart class module working, with events, so that an embedded chart responds to events? Stephen Bullen and Andy Pope are names to Google for and here are some more links: VBA Techniques | Produce Excel Charts That Look Like What You Had In Mind | InformIT VBA Techniques | Produce Excel Charts That Look Like What You Had In Mind | InformIT Get mouse pointer position in chart coordinates Get mouse pointer position in chart coordinates Interactive Chart in VBA using Mouse Move Event | Excel & VBA – Databison Interactive Chart in VBA using Mouse Move Event | Excel & VBA – Databison […]

  2. How to deal with two charts and their events wrote:

  3. Trouble with protected sheets and vba wrote:

    […] to you, a number of events that can be used to make them interactive. have a look here for a guide:Interactive Chart in VBA using Mouse Move Event | Excel & VBA – Databison […]

  4. Mouseover Value Changes Affected by Points in a Chart? wrote:

    […] document.write(''); It can be done with VBA: Interactive Chart in VBA using Mouse Move Event | Excel & VBA – Databison […]

  5. Interactive Line Graph, mouseover to move vertical line that will intersect with y axis and display value wrote:

    […] document.write(''); Hello See if you can adapt this: Interactive Chart in VBA using Mouse Move Event | Excel & VBA – Databison […]


Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel