CODEWITHSUNDEEP
vbaexcelchartsexcel-charts
Angelin Calu
Angelin Calu

Reputation: 1915

Excel 2013 Clustered Column Chart in VBA

I am using EXCEL VBA to create a new Chart from Two Rows of Data. Excel version is 2013.

On the X axis I have years and on the Y axis I have some specific values for each year.

The problem I'm facing is that when I'm creating the actual Chart, It will randomly chose one of the three styles avaiable, as shown in the screen capture below.

I am creating the chart with:

ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Union(Sheets(SheetName).Range(Cells(1, 11), Cells(1, LastColumnNumber)), Sheets(SheetName).Range(Cells(4, 11), Cells(4, LastColumnNumber)))

The reason for adding Sheets(SheetsName) is the fact that I am moving the chart to a new sheet after creating it, so that's needed for keeping the references.

LastColumnNumber is used to count the colum for the last year and it's corresponding value.

I have tried:

ActiveChart.ChartType = xlColumnClustered

Also I have tried recording a new macro when changing styles, and there is no parameter choosing one of those 3.

Excel 2013 Chart Type Selection

As a workaround (but it doesn't work in VBA), I am changing the chart style to something else than Clustered Column style and than choose back the one that I need.

P.S. I actually need the one selected in the print screen attached.

How Can I force that style to be applied?

Upvotes: 0

Views: 5849

Answers (1)

Preston
Preston

Reputation: 8187

As I understand it, you need this:

Chart.PlotBy = xlColumns

Or this:

Chart.PlotBy = xlRows

See below for context:

Sub InsertBar(myRange As Range)

    Dim rngChart As Range
    Dim myChart As Chart

    Sheets(Operator.Value).Range("$A$10:$C$10").Select
    Set myChart = ActiveSheet.Shapes.AddChart(xlColumnClustered, 500, 10, , 175).Chart

    With myChart
        .PlotBy = xlColumns
        .ChartArea.Format.TextFrame2.TextRange.Font.Size = 8
        .HasTitle = True
        .ChartTitle.Text = "Title"
        .SeriesCollection(1).Name = Range("B" & StartRow - 1).Value
        .SeriesCollection(2).Name = Range("C" & StartRow - 1).Value
    End With
End Sub

Upvotes: 1

Related Questions