CODEWITHSUNDEEP
exceleventschartsvba
e700k
e700k

Reputation: 136

Calculating datapoint position with chart mouseover event

I have a chart embedded on a sheet (X, Y scatter). I also have a mouseover event on the chart which - when you hover over a datapoint - prints the selected value of the corresponding datapoint to a cell. This works with using the .GetChartElement method.

However, I'd also like to add a feature with you can add an arrow (or line) connecting a datapoint and a predefined, named cell. I also managed to do this but unfortunately my calculations aren't accurate enough because the datapoint end of the line is never at the point exactly but somewhere around it (See picture).

lines almost at the right place

The way I did this:

Private Sub myChartClass_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal X As Long, ByVal Y As Long)

Me.myChartClass.GetChartElement X, Y, ElementID, Arg1, Arg2

Set chrt = ActiveSheet.ChartObjects(1).Chart
Set ser = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)

chart_data = ser.Values
chart_label = ser.XValues

YValue = chart_data(Arg2)
XValue = chart_label(Arg2)

'[Code to print corresponding values]

If addComment = True Then Call wks35.addComment(XValue, YValue)

This way I can either pass the coordinates (X, Y variables) and the actual values on the axes (XValue, YValue). Above, the latter is used.

Public Sub addComment( _
                       ByVal X As Double, _
                       ByVal Y As Double _
           )

Dim chartObj As Chart
Dim chartWidth As Double
Dim chartHeight As Double
Dim l1 As Long, l2 As Long, r1 As Long, r2 As Long

With wks35

    Set chartObj = .ChartObjects(1).Chart

    chartWidth = chartObj.PlotArea.Width
    chartHeight = chartObj.PlotArea.Height

    Y = chartHeight - (chartHeight * ((Y - chartObj.Axes(xlValue).MinimumScale) _
    /(chartObj.Axes(xlValue).MaximumScale - chartObj.Axes(xlValue).MinimumScale)))
    X = chartWidth * ((X - chartObj.Axes(xlCategory).MinimumScale) / _
    (chartObj.Axes(xlCategory).MaximumScale - chartObj.Axes(xlCategory).MinimumScale))

    l1 = Range("Comment1").Left
    l2 = Range("Comment1").Top
    r1 = X + ActiveSheet.ChartObjects(1).Left + chartObj.PlotArea.InsideLeft
    r2 = Y + ActiveSheet.ChartObjects(1).Top + chartObj.PlotArea.InsideTop
    With ActiveSheet.Shapes.AddLine(l1, l2, r1, r2).Line
        .ForeColor.RGB = RGB(255, 0, 0)
    End With

End With

End Sub

So basically the idea was to have the values on the axes and by calculating its relative position considering the minimum and maximum values on the axis get the datapoint's absolute top and left value from the borders of the plotarea. Then adding the difference of the chart and the plotarea and then the charts top and left.

It may be long and difficult to follow, but I appreciate any help.

Upvotes: 1

Views: 2614

Answers (1)

e700k
e700k

Reputation: 136

Whatever, I figured it out.

I used this

chartWidth = chartObj.PlotArea.InsideWidth
chartHeight = chartObj.PlotArea.InsideHeight

instead of this

chartWidth = chartObj.PlotArea.Width
chartHeight = chartObj.PlotArea.Height

It was almost there so I added some constants to the end (10 to left, 3 to top) the lines are at their place.

Upvotes: 1

Related Questions