Can you help me understanding how to operate with charts in VBA

[antlz]

Hi Forum!

This is the first time I try to work with charts in Excel.
I need to create charts using macros, in Excel 2010, but I can't figure it out what's the problem. First I tried it the easier way, at least I thought that would be it, using record macro. After I have finished, what I've intended to do, I re-ran the macro, to check if it worked, but it didn't.

This was the code for it:

    Sheets("Sheet1").Select
    Range("M13:N14").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlPie
    ActiveChart.SetSourceData Source:=Range( _
        "'Sheet1'!$M$13:$N$14")
    ActiveChart.ApplyLayout (6)
    ActiveChart.Legend.Select
    ActiveChart.Legend.legendentries(2).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(146, 208, 80)
        .Transparency = 0
        .Solid
    End With
    ActiveChart.ChartArea.Select
    ActiveSheet.Shapes(ActiveChart).IncrementLeft 74.25
    ActiveSheet.Shapes(ActiveChart).IncrementTop 144
    Range("M18").Select
    ActiveSheet.ChartObjects("Diagram 1").Activate

When I ran the macro, I got run-time error '-2147467259 (80004005)
And the yellow arrow points at the " With Selection.Format.Fill" line
I wanted to recolor the charts.


It turned out, I need to work with more than one chart.
So this problem became bigger.
[TABLE="width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD]2013.10
[/TD]
[TD]2013.11
[/TD]
[TD]2013.12
[/TD]
[TD]2014.01
[/TD]
[TD]2014.02
[/TD]
[/TR]
[TR]
[TD]SU
[/TD]
[TD]Number of sold units
[/TD]
[TD]200
[/TD]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MY
[/TD]
[TD]Number of trees in my garden
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]QV
[/TD]
[TD]Number of questions I have about VBA
[/TD]
[TD]150
[/TD]
[TD]60
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


These cells get their values, after I run a macro.
What I need is, after refreshing the value of a cell, or add new, it needs to generate a chart(mostly columns), in "Graphs" sheet. The chart's name would be like: Chart_(SU or MY or QV or whatever is in the first cell of the row.

The range of rows may get bigger eventually.
Whenever I refresh the values, I need to recreate the chart, so I need to delete the existing one first. I haven't checked it, but I guess you can use the method:
For Each ch In Charts? - or something else? I have never worked with charts before in VBA
If ch.Name Like "*SU" Then flg = True: Exit For
Next
If flg = True Then
ch.Delete

CODE for adding new chart.

Else

CODE for adding new chart.

End If

I have tried a different approach, but it isn't working either.

    Sheets("Sheet1").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SeriesCollection(1).Values = "=Sheet1!$G$20:$K$20"
    ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$G$2:$K$2"
    ActiveChart.Location Where:=xlLocationAsObject, _
        Name:="Graphs"
    With ActiveChart.Parent
        .Top = Range("C2").Top
        .Left = Range("C2").Left
        .Width = Range("C2:J13").Width
        .Height = Range("C2:J13").Height
    End With

I don't know why, but it has some problem with the ActiveChart.SeriesCollection(1).Values = "=Sheet1!$G$20:$K$20" line


And if its not overhelming yet, I need to add a line to each chart, which symbolises the limit (for example in the number of trees in my garden chart, it is not acceptable to have 4 unit, so I need to add this line, to help visualizing it, and also recolor those columns which over/underextends its value.

Thanks in advance,
Z

Ps: I need your help badly, because I have no experience in working with charts in VBA.

 

[Worf]

Hi
Let’s do this stepwise. Please test the code below; when it’s working for you, we can move on to the other topics you mentioned…

Sub Recolor()


ActiveSheet.Shapes.AddChart.Select
With ActiveChart
    .ChartType = xlPie
    .SetSourceData Source:=Range("'Sheet1'!$M$13:$N$14")
    .ApplyLayout (6)
    ' one slice blue, the other red
    .SeriesCollection(1).Points(1).Interior.Color = RGB(2, 6, 248)
    .SeriesCollection(1).Points(2).Interior.Color = RGB(250, 6, 8)
End With
ActiveSheet.ChartObjects("Diagram 1").Activate


End Sub


Sub ColCluster()
Dim s As Series, ws As Worksheet


Set ws = Sheets("Graphs")
ws.Activate


ActiveSheet.Shapes.AddChart.Select
With ActiveChart
    .Location Where:=xlLocationAsObject, Name:="Graphs"
    With .Parent
        .Left = ws.Range("C2").Left
        .Width = ws.Range("C2:J13").Width
        .Height = ws.Range("C2:J13").Height
        .Top = ws.Range("C2").Top
    End With
    .ChartType = xlColumnClustered
    Set s = .SeriesCollection.NewSeries
    s.Values = "=Sheet1!$G$20:$K$20"
    s.XValues = "=Sheet1!$G$2:$K$2"


End With
End Sub