VBA ChartObject change name of series in the legend

Question

I am working on a dashboard that has two sheets one named: "Current DMB Template" and 2nd named "KPI". In main tab there is two columns green one D8:E27 and red one G8:H27.

In KPI there are 4 tables.

Thanks to help of another member here, I have a macro that will create a chart for each table from KPI in either green or red column depending on if the KPI was achieved or not:

Sub DeleteKPICharts()

    On Error Resume Next

    Worksheets("Current DMB Template").ChartObjects.Delete

End Sub

Sub KPIcharts()
Dim wb As Workbook
Dim main As Worksheet
Dim KPI As Worksheet

Dim embeddedchart As ChartObject
Set wb = ThisWorkbook
Set main = wb.Sheets("Current DMB Template")
Set KPI = wb.Sheets("KPI")

Dim TotalGraphsD As Byte
Dim TotalGraphsG As Byte
Dim i As Long, j As Long
Dim ThisColumn As String


For i = 2 To 11 Step 3 'loop trough 2-8 in breaks of 3-> 2,5,8
    ThisColumn = IIf(KPI.Range("E" & i).Value = "No", "G", "D") 'which column will be the graph
    j = 9 + (7 * IIf(ThisColumn = "G", TotalGraphsG, TotalGraphsD))  'which row will be the graph, first in row 9 in steps of 7 so, first in row 9, second row 16, third row 23 and so on...
    Set embeddedchart = main.ChartObjects.Add(Left:=Range(ThisColumn & j).Left, Width:=170, Top:=Range(ThisColumn & j).Top, Height:=100)
    embeddedchart.Chart.SetSourceData Source:=KPI.Range("A" & i & ":C" & i)
    
    If ThisColumn = "G" Then TotalGraphsG = TotalGraphsG + 1 Else TotalGraphsD = TotalGraphsD + 1
Next i


End Sub

Now I face another problem. In every case each chart has a legend that says Series 1 and Series 2.

I tried changing the name with .SeriesNameLevel = xlSeriesNameLevelAll but I get the message about wrong method/property. Please, help. I need series to be named Realization and Target respectively. Many thanks in advance.

Answer 1

Try this:

Sub test()
Dim wb As Workbook
Dim main As Worksheet
Dim kpi As Worksheet

Dim embeddedchart As ChartObject
Set wb = ThisWorkbook
Set main = wb.Sheets("Current DMB Template")
Set kpi = wb.Sheets("KPI")

Dim TotalGraphsD As Byte 'will store how many graphs in D
Dim TotalGraphsG As Byte ' will store how many graphs in G
Dim i As Long, j As Long
Dim ThisColumn As String


For i = 1 To 25 Step 3 'loop trough 1-25 in breaks of 3-> 1,4,7
    ThisColumn = IIf(kpi.Range("E" & (i + 1)).Value = "no", "G", "D") 'which column will be the graph
    j = 9 + (7 * IIf(ThisColumn = "G", TotalGraphsG, TotalGraphsD))  'which row will be the graph, first in row 9 in steps of 7 so, first in row 9, second row 16, third row 23 and so on...
    Set embeddedchart = main.ChartObjects.Add(Left:=Range(ThisColumn & j).Left, Width:=170, Top:=Range(ThisColumn & j).Top, Height:=100)
    With embeddedchart.Chart
        .SetSourceData Source:=kpi.Range("B" & i & ":C" & (i + 1)) 'source data
        .ChartWizard , xlColumn, , xlColumns, , , True 'plot by cols
        .SeriesCollection(1).XValues = "=" & kpi.Range("A" & (i + 1)).Address(True, True, xlA1, True, True) 'title
    End With
    If ThisColumn = "G" Then TotalGraphsG = TotalGraphsG + 1 Else TotalGraphsD = TotalGraphsD + 1
Next i


End Sub

Notice I've edited the loop and part of the inside code. Also, expected output may be different on recent Excel versions (mine is 2007...).

I've used some interesting objects to get this:

ChartObjects.Add method (Excel)

Chart.ChartWizard method (Excel)