Changing chart properties using .chart

[Human_doing]

Hi all,

This VBA creates a chart on each worksheet in the workbook, can anyone please tell me how to change the background of the chart from gray to white and the color of series 2 from pink (the default for a second series I presume) to green? Also is there a list anywhere of all chart properties that you can change using .chart?

Sub ChartYearComparison()
Dim WS As Worksheet
Dim Rng As Range
Dim Rng1 As Range
For Each WS In Worksheets
        Set Rng = WS.Range("A200:C213")
        Set Rng1 = WS.Range("A11:L21")
    With WS.ChartObjects.Add _
            (Left:=Rng1.Left, Width:=Rng1.Width, Top:=Rng1.Top, Height:=Rng1.Height)
        .Chart.SetSourceData Source:=WS.Range("A200:C213")
        .Chart.ChartType = xlColumnClustered
        .Chart.HasTitle = True
        .Chart.ChartTitle.Characters.Text = WS.Range("A2") & " Occurences"
        .Chart.SeriesCollection(2).ChartType = xlLine
    End With
 
 
Next WS
 
End Sub

 

[Andrew Poulsom]

Use the Object Browser in the Visual Basic Editor to see the Chart object's properties and methods. You can also use the Macro Recorder to get the VBA code that you want.

 

[Human_doing]

Hi Andrew,

Thanks for your quick response, I had thought about using the macro recorder but I'm not sure how to add what it produces in to my code as it doesn't seem to 'match' very well what I'm doing. E.g. when I record a macro to make the back of the chart blank, it produces this:

Sub Macro1()
    ActiveSheet.ChartObjects("Chart 13").Activate
    ActiveChart.PlotArea.Select
    With Selection.Border
        .ColorIndex = 16
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    With Selection.Interior
        .ColorIndex = 2
        .PatternColorIndex = 1
        .Pattern = xlSolid
    End With
End Sub

But how would I add this in to my original code to get it to occur on this chart and as part of the loop through all worksheets on the workbook?

Thanks

 

[Andrew Poulsom]

Try:

Sub ChartYearComparison()
    Dim WS As Worksheet
    Dim Rng As Range
    Dim Rng1 As Range
    For Each WS In Worksheets
        Set Rng = WS.Range("A200:C213")
        Set Rng1 = WS.Range("A11:L21")
        With WS.ChartObjects.Add _
            (Left:=Rng1.Left, Width:=Rng1.Width, Top:=Rng1.Top, Height:=Rng1.Height)
            With .Chart
                .SetSourceData Source:=WS.Range("A200:C213")
                .ChartType = xlColumnClustered
                .HasTitle = True
                .ChartTitle.Characters.Text = WS.Range("A2") & " Occurences"
                .SeriesCollection(2).ChartType = xlLine
                With .PlotArea.Border
                    .ColorIndex = 16
                    .Weight = xlThin
                    .LineStyle = xlContinuous
                End With
                With .PlotArea.Interior
                    .ColorIndex = 2
                    .PatternColorIndex = 1
                    .Pattern = xlSolid
                End With
            End With
        End With
    Next WS
End Sub

 

[Human_doing]

Hi,

thanks for that I am so appreciative of your help that's great. I am trying to also get the chart to show the values for series 1 only, can you please explain why:

.SeriesCollection(1).DataLabels.ShowValue = True

Is giving me error runtime error 1004? The code I'm trying is:

Sub ChartMachineYearComparison()
    Dim WS As Worksheet
    Dim Rng As Range
    Dim Rng1 As Range
    For Each WS In Worksheets
        Set Rng = WS.Range("A200:C213")
        Set Rng1 = WS.Range("A11:L21")
        With WS.ChartObjects.Add _
            (Left:=Rng1.Left, Width:=Rng1.Width, Top:=Rng1.Top, Height:=Rng1.Height)
            With .Chart
                .SetSourceData Source:=WS.Range("A200:C213")
                .ChartType = xlColumnClustered
                .HasTitle = True
                .ChartTitle.Characters.Text = WS.Range("A2") & " Occurences"
                .SeriesCollection(2).ChartType = xlLine
                .SeriesCollection(1).Name = "=""Falls"""
                .SeriesCollection(2).Name = "=""20% Reduction"""
                .SeriesCollection(1).DataLabels.ShowValue = True
                                
                    With .PlotArea.Border
                    .ColorIndex = 16
                    .Weight = xlThin
                    .LineStyle = xlContinuous
                End With
                With .PlotArea.Interior
                    .ColorIndex = 2
                    .PatternColorIndex = 1
                    .Pattern = xlSolid
                End With
                
            End With
        End With
    Next WS
End Sub

Thanks

 

[Andrew Poulsom]

I expect you have to set the HasDataLabels property to True first.

 

[Human_doing]

Thanks that has sorted it, much obliged.