Center Data Label over XY scatter with lines

Question

I am in the process of creating the following chart:

But I need each data label to be centered over the line that gets created instead of the end point of the line. I have the following:

    Set mypts = mysrs.Points
    mypts(mypts.Count).ApplyDataLabels

        With mypts(mypts.Count).DataLabel
            .ShowSeriesName = True
            .ShowCategoryName = False
            .ShowValue = False
            ' optional parameters
            .Orientation = 0
            .Position = xlLabelPositionAbove
            .Font.Size = 10
            .Font.Bold = True
        End With

Does any body know how to center the data labels, which I gave it by giving each series a name?

Answer 1

Centering a label above each line will involve creating another data series that calculates the line's center. Without having access to your data, I turned to @JonPeltier 's excellent post on connecting two XY data series.

Your question involves your custom VBA code. My example here should be taken as the steps needed to set up the data. At your choice you can either set up your data on the worksheet, or have your VBA code create this "extra" data series to add to the chart (again, because I don't know what your data looks like I can't recommend a solution specific to your problem).

I won't repeat all the details of creating the chart, but here's an example of the data and the resulting chart:

Now, setting up the mid-point data isn't difficult. The mid-point is a quick formula =((D2-A2)/2)+A2, copied down the column.

Added to the chart, select the "MidPoint" and "Value" columns and cntl+c copy the data, then select the chart and "Paste Special" as a new data series.

A Quick Note: DO NOT put your mid-point data on the same rows as the A and B data. I did this initially and Excel (in its own private wisdom) associated the data with column A. The results on the chart aren't pretty.

Next to last step is to select the new data series and select "Add Data Labels", then check the box for "Value From Cells". Choose the data range for the labels, e.g. cells C17:C20.

Finally, format the labels to only show the "Value From Cells" (uncheck the "Y" data label) and set the marker to "None". Then you have the centered label for your line.