Scatter graph labels in VBA (excel xp)

Topic

New Reply

I need to know if it’s possible to draw the 3 lines i have on my graph using code. The x and y coordinates will be given to me and i need to plot them on the graph. I will have 50 of these chart sheets so i want to see if there is a way i can write a macro to draw them.

Also, I only have 2 series on the scatter graph. There will probably be 20-30 and i’m afraid that the labels will overlap and i will have to manually move them about. Is there a way to control the data labels from not touching each other? Thank you for the help.

Reply | Quote

Replies

> draw the 3 lines
Just add a new series for each line (without points, just lines)

> Is there a way to control the data labels from not touching each other
Excel just places them on the right (but you can change this, look at the properties of DataLabels) and makes no effort to keep them from touching. You can specify the top and left of each DataLabel, but it would be a large effort to figure out how to keep them from touching and still make them readable. Just figure out the point density and turn off the DataLabels when it gets too high. HTH –Sam

Reply | Quote

I know how to add a new series but not “without points, just lines”. Can you help me with that. thanks

Reply | Quote

I’m working on an example. Meantime, record a macro that adds a new series and then formats that series setting Markers to None and Lines to Automatic. –Sam

Reply | Quote

ok, thanks

Reply | Quote

Here’s some sample code. See attachment to watch it plot and view the entire macro, but here is the plotting stuff:

    With Charts.Add
        .ChartType = xlXYScatter
'       Create main scatter plot with just markers
        .SetSourceData Worksheets("Sheet1").Range("A2:B21")
        With .Axes(xlCategory)
            .HasMajorGridlines = False
            .HasMinorGridlines = False
        End With
        With .Axes(xlValue)
            .HasMajorGridlines = False
            .HasMinorGridlines = False
        End With
        .HasLegend = False
        .PlotArea.ClearFormats
        With .Axes(xlValue)
            .MinimumScale = yMin
            .MaximumScale = yMax
            .Crosses = xlCustom
            .CrossesAt = yMin
            .MinorTickMark = xlOutside
        End With
        With .Axes(xlCategory)
            .MinimumScale = xMin
            .MaximumScale = xMax
            .Crosses = xlCustom
            .CrossesAt = xMin
            .MinorTickMark = xlOutside
        End With
'       Put value datalabels on min & max point
        With .SeriesCollection.NewSeries
            .XValues = Array(xLine1(1), xLine2(1))
            .Values = Array(yLine1(1), yLine2(1))
            .Border.LineStyle = xlNone
            .MarkerStyle = xlNone
            .ApplyDataLabels xlDataLabelsShowValue
            .DataLabels.NumberFormat = "0.0"
        End With
'       Plot regression line & a vertical line & a horizontal line
        For i = 2 To 4
            With .SeriesCollection.NewSeries
                .XValues = Array(xLine1(i), xLine2(i))
                .Values = Array(yLine1(i), yLine2(i))
                .Border.LineStyle = xlContinuous
                .Border.ColorIndex = 1
                .MarkerStyle = xlNone
            End With
        Next i
        .Deselect
    End With

Reply | Quote

Another thought regarding datalabels. If I have a few “important” data points on my chart that I really want to label, then I will create another series with just these points, turn-off markers & lines, and turn-on data labels.

Reply | Quote