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.

data-for-interactive-chart-vba

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.

insert-chart-as-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.
chart-in-a-chart-sheet

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.

elementid-of-a-chart-in-vba-interactive-chart

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.

interactive-chart-vba

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
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.Solid
        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
   
Else
    txtbox.Delete
    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



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

    regards,

    Amith

  2. Ajay wrote:

    Hi Amith,

    I don’t but I think JP’s site (http://www.codeforexcelandoutlook.com/) would be a good place to start.

    Regards,

  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:

    Marco,
    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 !
    Regards,
    Ajay

  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?



Translate

Chinese (Simplified)DutchEnglishFilipinoFrenchGermanHebrewHindiItalianJapanesePortugueseRussianSpanish

treeemap software for excel