Custom Chart Data Labels using VBA

[bahllr]

I appreciate any help you all can give me. I have been playing with this for a few hours now.

I am trying to create a dynamic control chart that has 5 series (Upper Control Limit (UCL), Lower Control Limit (LCL), Average (X) and Upper Spec Limit (USL) and Lower Spec Limit (LSL).

I have dynamic named ranges for each of these five series.


I am using VBA to add a data label to the last data point each time the chart is updated. The code I am using is below. My problem is that for the USL and LSL series, I keep getting application defined or object defined errors for the 'USL' and 'LSL' labels, respectively. I am using the same code to label all 5 series and it works for 3 of the 5. Not sure why this would throw an error. Also, it allows me to name it 'L' but not 'USL/LSL'.

As you can see by series 5, I have even tried creating a link to a cell for testing. This did not work either.
Any thoughts?

For n = 1 To 5


Sheets("Results").ChartObjects("Chart 3").Activate
ActiveChart.FullSeriesCollection.Select
ActiveChart.FullSeriesCollection.Points(r).ApplyDataLabels
ActiveChart.FullSeriesCollection.DataLabels.Select

If n = 1 Then
Selection.NumberFormat = "#,##0.00" & " = UCL"
ElseIf n = 2 Then Selection.NumberFormat = "#,##0.00" & " = LCL"
ElseIf n = 3 Then Selection.NumberFormat = "#,##0.00" & " = X"
ElseIf n = 4 Then Selection.NumberFormat = "#,##0.00" & " = L"
ElseIf n = 5 Then Selection.DataLabel.Text = "Saturday"

End If

 

[bahllr]

Bump. Any idea on why I can't hardcode 'USL' into a datalabel?