CODEWITHSUNDEEP
excelvba
oaklodge
oaklodge

Reputation: 748

Excel VBA to set axis labels

I have a table in Excel, something like this:

        sprint1    sprint2    sprint3
data1      5          6          7
data2      3          7          1
data3      1          7          9

I have this code:

Public Sub CreateChart(name As String, rng As Range)
    Dim cht As Object
    Set cht = ActiveSheet.Shapes.AddChart2
    cht.chart.SetSourceData Source:=rng
    cht.chart.ChartType = xlXYScatterLines
    cht.chart.HasTitle = True
    cht.chart.ChartTitle.Text = name
End Sub

I get a chart with the series correctly labeled (data1, data2, data3) in the legend and numbers labeling the y-axis as I expected. However the x-axis is labeled 0-10, it's not using the header row of the data (sprint1, sprint2, sprint3) as x-axis labels. What VBA magic do I need to invoke for this?

I thought it was something like:

cht.chart.Axes(xlCategory, xlPrimary).CategoryNames = some_range

But it doesn't seem to do anything.

Upvotes: 1

Views: 1389

Answers (1)

oaklodge
oaklodge

Reputation: 748

I think the root of the problem is that the chart type does not support this. I changed this line of code:

cht.chart.ChartType = xlXYScatterLines

to this:

cht.chart.ChartType = xlLineMarkers

and now it works, the x-axis labels are the sprint names.

Upvotes: 1

Related Questions