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

antlz

New Member
Joined
Oct 28, 2013
Messages
16
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:
Code:
    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.
2013.10
2013.11
2013.12
2014.01
2014.02
SU
Number of sold units
200
6
MY
Number of trees in my garden
3
2
QV
Number of questions I have about VBA
150
60

<TBODY>
</TBODY>


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.

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

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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…

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

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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