Using a named range in VBA for chart data labels

[FryGirl]

I have a drop down box with a Yes or No which calls the macro below to add data labels to my chart, not just the y and x value, but rather, value from cells. Of course the macro below is just for the Yes side of the drop down and that I have all done.

There is a named range for the values, but not getting the right syntax for adding the named range to the code below. Included is what I tried. But no luck.

Sub Macro4()
'    Dim rng As Range
'    Set rng = Worksheets("Linest").Range("MyLabels")
    ActiveChart.FullSeriesCollection(1).DataLabels.Select
'    ActiveChart.SeriesCollection(1).DataLabels.Format.TextFrame2.TextRange.InsertChartField msoChartFieldRange, rng, 0
    ActiveChart.SeriesCollection(1).DataLabels.Format.TextFrame2.TextRange.InsertChartField msoChartFieldRange, "=Linest!$BD$16:$BD$33", 0
    Selection.ShowRange = True
    Selection.ShowValue = False
    Application.Goto [A1], Scroll:=True
End Sub

Can somebody point me in the right direction?

 

[Domenic]

You will need to make sure that your series has data labels before running your code. You can use the ApplyDataLabels method of the Series object in order to apply the labels. Maybe something like this...

Option Explicit

Sub AddDataLabelsFromCells()


    Dim targetChart As Chart
    Dim labelRange As Range
    
    Set targetChart = ActiveChart
    
    If TypeName(targetChart) <> "Chart" Then
        MsgBox "Select a chart, and try again!", vbExclamation
        Exit Sub
    End If
    
    Set labelRange = Worksheets("Linest").Range("MyLabels")
    
    With targetChart.SeriesCollection(1)
        .ApplyDataLabels
        With .DataLabels
            .Format.TextFrame2.TextRange.InsertChartField msoChartFieldRange, labelRange.Address(External:=True), 0
            .ShowCategoryName = False
            .ShowRange = True
            .ShowSeriesName = False
            .ShowValue = False
        End With
    End With
    
End Sub

However, to avoid having to select your chart before running your macro, you can specify your chart by name instead of referring to the active chart. To do so, simply replace...

    Set targetChart = ActiveChart
    
    If TypeName(targetChart) <> "Chart" Then
        MsgBox "Select a chart, and try again!", vbExclamation
        Exit Sub
    End If

with

    Set targetChart = Worksheets("Linest").ChartObjects("Chart 1").Chart

Change the sheet and chart names, as necessary.

Hope this helps!

 

[FryGirl]

Hi Domenic,

This did indeed help. Thank you very much.

 

[Domenic]

You're very welcome!

Cheers!