graph point labeling via macro on Mac version of Excel

[roscoe]

I have a financial graph of multiple account values over time. Each column is a quarter, and as each quarter passes over time I add a new column left of the current data. All of the data except the current (rightmost in the table and on the graph) is static whereas the current data is formula-driven. On the graph, most of the past data has data labels using the "default" format (a few have been manually edited to highlight something); the current (rightmost) data uses a custom label.

Here's the issue and the question...When it becomes time to add the new quarterly column, my intent is for the custom labels to stay with the current (rightmost) data and the new column data picks up the default format. Alas, that's proving impossible to do easily. No matter how I do it, the newly added column picks up the custom formatting and the original rightmost data changes to the default formatting. I've tried copying the rightmost column to the right, then replacing the previous column with the required static data, and I've tried inserting a blank column left of the rightmost data and then filling in the gaps. Neither works in terms of the data label formatting; I have to manually fix the labeling for ten data points (the last two points on five different sets of data). I'm tired of doing that. There's has to be a way.

Before anyone says "use a macro" which is of course obvious, I'm doing this on a Mac, and all of my previous macro work (somewhat extensive) has been on a PC...and all that was on a work computer to which I no longer have access.

Any hints (links would be fine) to a macro that will loop through selected data sets (not all of them are current, some old account data is plotted for historical reference), and set the last two points only.

Thanks!

(Standing by to clarify my rather complex question)

 

[roscoe]

Anyone?

 

[roscoe]

Disregard...I figured out a macro.

 

[Peter_SSs]

Good to hear you figured out a solution.
If you would like to post the solution then it is perfectly fine to mark that post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[roscoe]

I thought marking it as answered would close the thread...but OK

 

[Peter_SSs]

I thought marking it as answered would close the thread.

Fair enough, however we generally don't "close" threads in these forums (unless something untoward has happened in the thread) as it is always possible that a better answer might subsequently be posted even after the OP has accepted an answer. Anyway, all good here now.

 

[roscoe]

Sub Update_labels()

ActiveSheet.ChartObjects("Chart 3").Activate

Dim iSrs As Long
Dim iPts As Long
For iSrs = 2 To 6 'ActiveChart.SeriesCollection.Count
    
'2= Green series
'3= Orange series
'4= Purple series
'5= Red series
'6= Maroon series

'Note that series "1" doesn't have labels so no editing required
    
    With ActiveChart.SeriesCollection(iSrs)
        iPts = .Points.Count
        With .Points(iPts) 'This corrects the last label in every series
            .ApplyDataLabels
            .DataLabel.ShowSeriesName = True
            .DataLabel.Position = xlLabelPositionRight
            .DataLabel.Separator = "-"
            .DataLabel.Font.Name = "Calibri"
            .DataLabel.Font.FontStyle = "Bold"
            .DataLabel.Font.Size = 8
           '.DataLabel.NumberFormat = "#,##0.00"
        End With '.Points(iPts)
        
        Select Case iSrs. 'This corrects the second to last label in two of the series
       
        Case 2, 5
            With .Points(iPts - 1)
                .ApplyDataLabels
                .DataLabel.ShowSeriesName = False
                .DataLabel.Position = xlLabelPositionBelow
                .DataLabel.Font.Name = "Calibri"
                .DataLabel.Font.FontStyle = "Normal"
                .DataLabel.Font.Size = 8
               '.DataLabel.NumberFormat = "#,##0.00"
            End With '.Points(iPts - 1)
            
        Case 3, 4, 6 'This removes the second to last label in three other series
            With .Points(iPts - 1)
            If .HasDataLabel = True Then .DataLabel.Delete
            End With '.Points(iPts - 1)
            
        End Select
        
    End With 'ActiveChart.SeriesCollection(iSrs)
Next 'iSrs = 1 To ActiveChart.SeriesCollection.Count

End Sub

 

[roscoe]

To clarify why I did the select case statement (if it'd not obvious), is 3/5 series do not have labels on any but the last data point so I make sure they go away after the column insert moves the label to that point. The other 2/5 labels have every data point labeled and as such the second to last has to be reset to my "default".