Search code examples
excelvbadatatablesexcel-charts

Set Chart Series Name to Table Column Name instead of Series1,2,3,

With Excel 2010 I have the following code that creates a chart("BMS Data Chart") on a separate sheet from data from a table ("BMS_Data"). All works well except the Chart Series Names are Series1, Series2, etc... How can I use VBA to set these series names to the column header name of the table at time of creation?

Sub createBMSChart()
Dim cht As ChartObject
Dim ws As Worksheet
Dim myCht As ChartObject
Dim objSeries As Series
Dim srs As Series
Dim cWs As Worksheet
Dim tbl As Range

Sheets.add after:=Worksheets("BMS Data")
ActiveSheet.Name = "BMS Data Chart"

Set cWs = ActiveSheet
cWs.Tab.Color = vbGreen

Set ws = Worksheets("BMS Data")
Set tbl = ws.Range("BMS_Data")

Set cht = cWs.ChartObjects.add(Left:=10, Width:=1300, Top:=10, Height:=550)
cht.Chart.SetSourceData Source:=tbl
cht.Name = "BMS Data Chart"
Set myCht = cht

    With myCht.Chart
    .ChartType = xlLine
    .HasTitle = True
    .ChartTitle.Text = "BMS Data Chart"
    .Legend.Position = xlBottom
    .Axes(xlCategory).CategoryType = xlCategoryScale
    .Axes(xlCategory).TickLabelPosition = xlLow
    .Axes(xlCategory).MajorTickMark = xlNone
    .Axes(xlCategory).AxisBetweenCategories = False
    End With


    For Each srs In cWs.ChartObjects("BMS Data Chart").Chart.SeriesCollection
        srs.Format.Line.Weight = 1

    Next

cWs.Activate
End Sub

Partial Table Data Current Chart Image

Solution

  • Think you can do this by referencing the table and the header row will automatically be used as the series name. I've only changed the two starred lines below (change the table name as necessary):

    Sub createBMSChart()

Dim cht As ChartObject
Dim ws As Worksheet
Dim myCht As ChartObject
Dim objSeries As Series
Dim srs As Series
Dim cWs As Worksheet
Dim tbl As ListObject

Sheets.Add after:=Worksheets("BMS Data")
ActiveSheet.Name = "BMS Data Chart"

Set cWs = ActiveSheet
cWs.Tab.Color = vbGreen

Set ws = Worksheets("BMS Data")
Set tbl = ws.ListObjects("Table1")          '********************************************

Set cht = cWs.ChartObjects.Add(Left:=10, Width:=1300, Top:=10, Height:=550)
cht.Chart.SetSourceData Source:=tbl.Range   '********************************************
cht.Name = "BMS Data Chart"
Set myCht = cht

With myCht.Chart
    .ChartType = xlLine
    .HasTitle = True
    .ChartTitle.Text = "BMS Data Chart"
    .Legend.Position = xlBottom
    .Axes(xlCategory).CategoryType = xlCategoryScale
    .Axes(xlCategory).TickLabelPosition = xlLow
    .Axes(xlCategory).MajorTickMark = xlNone
    .Axes(xlCategory).AxisBetweenCategories = False
End With

For Each srs In cWs.ChartObjects("BMS Data Chart").Chart.SeriesCollection
    srs.Format.Line.Weight = 1
Next

cWs.Activate

End Sub

    enter image description here