none
get X and Y values of scatter plot points RRS feed

  • Question

  • I created a scatter plot in Access form, and I would like to change the dot color based on a third column. I used the following code, but it seems that the order of the points is different from the order of records in the datasheet. The dots are colored wrongly. How can I find the X and Y values of the points? I tried to use objChart.SeriesColelction(1).values (or xvalue), it didn't seem to exist.

        Dim objChart As Chart
        Dim objSheet As Object
        Set objChart = Me.chtPerf.Object.Application.Chart
        Set objSheet = Me.chtPerf.Object.Application.DataSheet

        For i = 1 To objChart.SeriesCollection(1).Points.Count        
            If objSheet.Cells(i + 1, 3).Value = "Yellow" Then
                objChart.SeriesCollection(1).Points(i).MarkerBackgroundColorIndex = 6
            End If
            If objSheet.Cells(i + 1, 3).Value = "Green" Then
                objChart.SeriesCollection(1).Points(i).MarkerBackgroundColorIndex = 4
            End If
            If objSheet.Cells(i + 1, 3).Value = "Red" Then
                objChart.SeriesCollection(1).Points(i).MarkerBackgroundColorIndex = 3
            End If
        Next
        Set objChart = Nothing
        Set objSheet = Nothing

    Wednesday, September 2, 2015 7:24 PM

Answers

  • Hi Jennifer,

    >> I could highlight the column and click Data -> Plot on X axis.

    In my option, you could set the PlotOnX of the chart object as “0”, here is a simple code:

    Private Sub Command2_Click()
         Dim objChart As Chart
         Dim objSheet As Object
         Set objChart = Me.Graph0.Object.Application.Chart
         Set objSheet = Me.Graph0.Object.Application.DataSheet
         Debug.Print objChart.PlotOnX
         objChart.PlotOnX = 0
    End Sub

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by Jennifer317 Wednesday, September 9, 2015 2:00 PM
    Wednesday, September 9, 2015 12:23 PM

All replies

  • Hi Jennifer317,

    >> it seems that the order of the points is different from the order of records in the datasheet. The dots are colored wrongly.

    I made a test with your code, but it worked correctly at my side. I suggest you check the Datasheet form whether you set it by row or by group. The picture below shows details about my test.

    >> How can I find the X and Y values of the points?

    I am afraid that it is impossible to get X and Y from the points. For a workaround, I think you could select point in the chart, right-click to get Format Data Series, check the X value in the Data Labels tab. And then it will show up like picture above. To get the data label, you could use the code below:

        Set p = objChart.SeriesCollection(1).Points(i)    
        Debug.Print objSheet.Cells(i + 1, 3).Value
        Debug.Print p.DataLabel.Text

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, September 4, 2015 7:29 AM
  • Hi, Edward,

    Thanks for your reply. I did find out that my code worked. The trouble I had was that my form didn't refresh and the color of the dots didn't match with the data.

    I have another issue with the chart in MS Access. The chart I have is in a subform which has a SQL statement as its RecordSource. The query qryForm looks like "select x, y, z from table where a=dropdown1.value and b=dropdown2.value". The Row Source for the Chart is "select x, y, z from qryForm" In the main form, as dropdown list is selected, the SQL for qryForm is updated (to plug in the selected value) and subform's RecordSource is updated as well. On form load, I changed the SQL with a where statement such that no records are returned so that I can get a blank chart. As dropdown list is selected, SQL and subform's RecordSource is updated, hence the chart is updated as well. All seems fine, except that after a few times of changing dropdown lists and closing the form, the chart design got changed somehow and the x-axis is not set (all dots show on the y-axis). I cannot insert an image here. But if you look at your chart above, you see that above your x column in the datasheet, there is an "X" to indicate that column x is used as X. That's how my chart was set up in design. But after a while, the chart design got changed automatically and if I go back to design view, that "X" is gone. I could highlight the column and click Data -> Plot on X axis. That will fix the problem. But then it happens again after a while. I'd like to set that in the VBA code as the subform of the chart load. How can I do that? This should be something simple, but I couldn't find anything by googling.

    Best,

    Jennifer

    Tuesday, September 8, 2015 8:29 PM
  • Hi Jennifer,

    >> I could highlight the column and click Data -> Plot on X axis.

    In my option, you could set the PlotOnX of the chart object as “0”, here is a simple code:

    Private Sub Command2_Click()
         Dim objChart As Chart
         Dim objSheet As Object
         Set objChart = Me.Graph0.Object.Application.Chart
         Set objSheet = Me.Graph0.Object.Application.DataSheet
         Debug.Print objChart.PlotOnX
         objChart.PlotOnX = 0
    End Sub

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by Jennifer317 Wednesday, September 9, 2015 2:00 PM
    Wednesday, September 9, 2015 12:23 PM
  • Thank you, Edward. That's exactly what I was looking for!

    Jennifer

    Wednesday, September 9, 2015 2:00 PM