Changing chart properties using .chart

H

Human_doing

Board Regular
Joined
Feb 16, 2011
Messages
137
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?

Code: 
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
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.
 
Upvote 0
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:

Code: 
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
 
Upvote 0
Try:

Code: 
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
 
Upvote 0
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:

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
Data formatting for Chart
Replies
3
Views
315
IREALLYambatman
I
N
  • Question
changing the color pallet of a chart with VBA
Replies
1
Views
101
rlv01
R
E
  • Question
Trying to put the chart below Sales Value
Replies
2
Views
188
Eric Penfold
E
Q
  • Question
Please help me, I am writing an excel vba chart1 code.
Replies
0
Views
531
Quan71
Q
B
  • Question
How do I export charts based on conditions to PDF using VBA?
Replies
1
Views
715
John_w
J

Forum statistics

Threads
1,214,659
Messages
6,120,786
Members
448,992
Latest member
prabhuk279

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top