Search code examples
vbaexcelchartsexcel-charts

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?


Solution

  • 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