Chart question

[Johny]

Hello,

I got an Excel.Chart.8-class object on my form which uses a scatter-type graph to display the data. This works perfectly. Now what I want is a new feature, dunno if it can even be done:
I want the user to make a selection of points in the graph with the mouse. Then, when the user clicks a the button named "selection" he can view the data of the selected points on my graph.

How can I do this? I notice the graph is acting like a picture on my form, so maybe placing another "invisible" object on top of it? Dunno I am just guessing.

Can anybody help meh?

Johny

 

[Johny]

Found some code on the internet that could help me

' ** Class module named Class1 **

Public WithEvents Ch As Chart

Dim IDNum As Long
Dim a As Long
Dim b As Long

Private Sub Ch_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    Dim Txt As String
    Txt = ""
    Ch.GetChartElement x, y, IDNum, a, b
    If IDNum = xlSeries Then
        With ActiveChart.SeriesCollection(a).Points(b)
             .HasDataLabel = True
            Txt = "Series " & .Parent.Name
            Txt = Txt & " point " & b
            Txt = Txt & " (" & .DataLabel.Text & ")"
            Txt = Txt & " - " & "other text here"
            With .DataLabel
                .Text = Txt
                .Position = xlLabelPositionAbove
                .Font.Size = 8
                .Border.Weight = xlHairline
                .Border.LineStyle = xlAutomatic
                .Interior.ColorIndex = 19
            End With
        End With
    End If
End Sub

Private Sub Ch_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    Ch.GetChartElement x, y, IDNum, a, b
    If IDNum = xlSeries Then
        With ActiveChart.SeriesCollection(a).Points(b)
            .HasDataLabel = False
        End With
    End If
End Sub

' ** Sheet module code to connect to the Class module **

Dim MyChart As New Class1

Private Sub Worksheet_Activate()
    Set MyChart.Ch = ActiveSheet.ChartObjects(1).Chart
End Sub

But my problem is I can't trigger an event when clicking the chart object on the form. When I click the chart with the mouse, the mouse_down event of the form is triggered instead of the mouse_down event of the chart.
The chart is an "unbound object frame" - Microsoft Excel chart.

I thought I could solve this by calculating the X & Y values of the "unbound object frame" where the chart is in, through the mouse_down event of the form. But then I got problems with the "GetChartElement x, y, IDNum, a, b" function: Wherever I click in the chart, I'll always get a same returnvalue for IDNum.
Maybe the soluation I found is not for an "unbound object frame" ?

The thing is, I got to implement this extra functionality in an already exisiting program so I can't make any big changes

Still nobody here that can help me?

 

[mschwent]

Did you try, from the form_Mouse_down event, translating to the coordinate system of the chart and then calling it?

Ch_MouseDown (...,..., X-Ch.Left, Y-Ch.Top)

It seems to me like it would take the X, Y coordinates of the form and then localize them to the chart, be just like it let you click in the chart. You'd have to add some error checking to verify the click is on the chart.

Not sure how GetChartElement works so I'm not sure on whether that will help you or not.

 

[Johny]

Don't know exactly what you mean.
I already tried to set some breakpoints in the Ch_MouseDown event but the code nevers gets executed even when I click on the chart.

Just did another example: dropped an "unbound object frame" on the form and pasted some dummy code in the mouse_down event of this new object and directly set a breakpoint, and again program never breaks.
Dunno if this problem and GetChartElement function problem are related

If I start from the mouse_down event of the form, I know for sure the nX and nY values are correctly initialised when I call the GetChartElement function

nX=X - Me.OLEgraph.Left
nY=Y - Me.OLEgraph.Top

but I don't get any return value

 

[Johny]

I found it!!

The unit of measure of the x and y values are in "twips" and the function works with pixels as unit. Googled and found a function to convert from twips to pixels at the MS site: http://support.microsoft.com/?kbid=210590

It works like a charm!

 

[mschwent]

Nice link, that actually helps with a problem I was having elsewhere. Tried to help ya but ended up getting helped instead...

 

[Johny]

Nice to hear I could help someone out

Does somebody know how I can get the x and y coordinate of the points on the series on a graph. Since the 'GetChartElement' function only works one way (knowing the x and y values you can look if there's a point on the graph) but now I want gather all the x and y values of all points on the graph.
I want to do this so I can let the user make a selection of an area on the graph and look which points are in the selected area.