CODEWITHSUNDEEP
excelvbacharts
Zacchini
Zacchini

Reputation: 143

How to highlight last data point in scatter plot using VBA

Wondering how to add the following feature of a scatterplot:

![chart

I want to identify the final data point and project it as a highlighted scatter dot like above

Here is my current code:

Sub Graph()

' Creates scatter chart with provided limit/percentile values
'

    Dim my_range    As Range, t

    t = Selection.Cells(1, 1).Value + " - " + ActiveSheet.Name

    Dim OldSheet As Worksheet
    Set OldSheet = ActiveSheet

    Set my_range = Union(Selection, ActiveSheet.Range("A:A"))
    ActiveSheet.Shapes.AddChart2(201, xlLine).Select
    With ActiveChart
        .FullSeriesCollection(1).ChartType = xlLine
        .FullSeriesCollection(1).AxisGroup = 1
        .FullSeriesCollection(2).ChartType = xlLine
        .FullSeriesCollection(2).AxisGroup = 1
        .FullSeriesCollection(1).ChartType = xlXYScatter
        .FullSeriesCollection(1).AxisGroup = 1
        .SetSourceData Source:=my_range
        .HasTitle = True
        .ChartTitle.Text = t
        .Location Where:=xlLocationAsObject, Name:="Graphs"
    End With
    OldSheet.Activate


End Sub

Upvotes: 0

Views: 641

Answers (1)

Tom Kennedy
Tom Kennedy

Reputation: 58

The last point is .SeriesCollection(1).Points(Points.count). At the top of your sub, add a variable to store the last data point position. After you've created the series, add whatever formatting code in a separate With statement in order to "highlight" it. An example that adds a data label is provided on Microsoft's online documentation of the points object: https://learn.microsoft.com/en-us/office/vba/api/excel.points(object)

It looks like you've already used Macro recording to get this far. Sometimes you get crap code out of that, but actually I find that recording formatting is a good use of the recorder, as chart formatting code isn't extensively covered many places online, and you have to go many objects/properties deep to do something as simple as color a data point blue or something.

In summary:

Dim LastPoint as Long
...
Set my_range = Union(Selection, ActiveSheet.Range("A:A"))
ActiveSheet.Shapes.AddChart2(201, xlLine).Select 
' when I recorded this, to go straight to an xychart, the code was
'  ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
LastPoint = ActiveChart.SeriesCollection(1).Points.Count
With ActiveChart
...
End With
With ActiveChart.SeriesCollection(1).Points(LastPoint) ' for example:
    .MarkerSize = 7
    .MarkerStyle = 8
    .Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2
End With

Upvotes: 2

Related Questions