I think what you are looking for is what I use to control the chart from Access when I export data. Change the source data, for series 1 to what ever to something similar to your legend as it is easier to work with. Running a macro would give you something similar to my code and there is a few options of where to place the label. I have had a little problem to display both the legend or identification line and the value in the same block and that is why there is two sections and it is display as a line chart. With Source data you can change the series to a name.
Code:
With ActiveChart
With ActiveChart.Axes(xlValue)
.MinimumScale = 30
.MaximumScale = 170
.MinorUnit = 1
' .MajorUnitIsAuto = True
'.Crosses = xlCustom
.CrossesAt = 30
.ReversePlotOrder = False
'.ScaleType = xlLinear
End With
With .SeriesCollection("Upper Control Limit").Points(19)
.HasDataLabel = True
.DataLabel.Text = " Upper Control Limit ="
.DataLabel.Font.Size = 7
.DataLabel.Font.Italic = True
.DataLabel.HorizontalAlignment = xlRight
.DataLabel.VerticalAlignment = xlTop
.DataLabel.Position = xlLabelPositionAbove
.DataLabel.Orientation = xlHorizontal
End With
With .SeriesCollection("Upper Control Limit").Points(24)
.HasDataLabel = True
' .DataLabel.Text = "Upper Control Limit = 2.3"
.DataLabel.Font.Size = 7
.DataLabel.Font.Italic = True
.DataLabel.HorizontalAlignment = xlRight
.DataLabel.VerticalAlignment = xlTop
.DataLabel.Position = xlLabelPositionAbove
.DataLabel.Orientation = xlHorizontal
End With
Bookmarks