Search code examples
excelvba

How to change the ChartType of an individual series?

I have a combo chart with multiple series and chart types.

I want to change the 'ChartType' of the first two data series using VBA.

Change Chart Type

This code is very similar to what I'd get if recording a macro and changing the series chart type manually.

[Selection_ChartType] is a named cell linked to a radio (option) button on the same sheet.

Sub ChartTypeSelect()
Dim slct As Integer

slct = [Selection_ChartType]
   
Call DisableUpdates

With ThisWorkbook.Sheets("Q_Hist").ChartObjects("Chart_Q_Hist")
    If slct = 1 Then
        .FullSeriesCollection(1).ChartType = xlColumnStacked    ' Run-time error '438' happens here
        .FullSeriesCollection(2).ChartType = xlColumnStacked
    ElseIf slct = 2 Then
        .FullSeriesCollection(1).ChartType = xlAreaStacked      ' Run-time error '438' happens here
        .FullSeriesCollection(2).ChartType = xlAreaStacked
    End If
End With

Call EnableUpdates
End Sub

Run-time error '438': Object doesn't support this property or method

ThisWorkbook.Sheets("Q_Hist").ChartObjects("Chart_Q_Hist").Select selects the intended chart, so I assume I'm referencing the object correctly.

Solution

  • FullSeriesCollection is a property of a Chart, not a ChartObject. Basically, a ChartObject is a container for a Chart, and that chart is available via the property Chart:

    So try

    With ThisWorkbook.Sheets("Q_Hist").ChartObjects("Chart_Q_Hist").Chart