Create several charts in one go using VBA

  • Thread starter Thread starter andreashermle
  • Start date Start date
A

andreashermle

Dear Experts:

below code creates a simple bar chart from a list (defined as table1)
on sheet 2.
I got several lists on sheet 1, all of them defined as tables, that is
Table2, Table3, and Table4

Is it possible to loop thru all these lists (defined as tables) in
sheet 2 and have the same bar chart created from all these lists
(defined as table1, table2, table3 and table4) in one go using VBA.

Help is very much appreciated. Thank you very much in advance.

Regards, Andreas

All of the charts should have the same Diagramm Title ($B$6)

Sub AddBarChart()
Dim myChtObj As ChartObject

Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=400, Top:=75, Height:=200)

With myChtObj.Chart
.SetSourceData Source:=Sheets("Sheet2").Range("Table1")
.ChartType = xlBarClustered
.HasLegend = False
.Axes(xlCategory).MajorTickMark = xlNone
.SeriesCollection(1).ApplyDataLabels
.SeriesCollection(1).DataLabels.Position =
xlLabelPositionInsideEnd
With .Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 10
.Delete
.MajorGridlines.Delete
End With

End With

With myChtObj.Chart
.SetElement (msoElementChartTitleAboveChart)
.ChartTitle.Text = "=Sheet2!$B$6"
End With


With myChtObj.Chart.Parent
.Top = Range("A7").Top
.Left = Range("E7").Left
.Name = "Chart1"
End With
 
Dear Experts:

below code creates a simple bar chart from a list (defined as table1)
on sheet 2.
I got several lists on sheet 1, all of them defined as tables, that is
Table2, Table3, and Table4

Is it possible to loop thru all these lists (defined as tables) in
sheet 2 and have the same bar chart created from all these lists
(defined as table1, table2, table3 and table4) in one go using VBA.

Help is very much appreciated. Thank you very much in advance.

Regards, Andreas

All of the charts should have the same Diagramm Title ($B$6)

Sub AddBarChart()
Dim myChtObj As ChartObject

    Set myChtObj = ActiveSheet.ChartObjects.Add _
        (Left:=100, Width:=400, Top:=75, Height:=200)

        With myChtObj.Chart
            .SetSourceData Source:=Sheets("Sheet2").Range("Table1")
            .ChartType = xlBarClustered
            .HasLegend = False
            .Axes(xlCategory).MajorTickMark = xlNone
            .SeriesCollection(1).ApplyDataLabels
            .SeriesCollection(1).DataLabels.Position =
xlLabelPositionInsideEnd
                With .Axes(xlValue)
                     .MinimumScale = 0
                     .MaximumScale = 10
                     .Delete
                     .MajorGridlines.Delete
                End With

        End With

        With myChtObj.Chart
            .SetElement (msoElementChartTitleAboveChart)
            .ChartTitle.Text = "=Sheet2!$B$6"
        End With

        With myChtObj.Chart.Parent
            .Top = Range("A7").Top
            .Left = Range("E7").Left
            .Name = "Chart1"
        End With
Click to expand...

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."
 
"If desired, send your file to dguillett  @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Don,

what a service! Will do the requested shorty.

Thank you. Regards, Andreas
 
Pseudocode:

For i=1 to 10
Set myChtObj = ... (choose position so no overlapping charts)
myChtObj.Chart.SetSourceData Source:= (appropriate i'th range)
Next

- Jon
 
Pseudocode:

For i=1 to 10
   Set myChtObj = ... (choose position so no overlapping charts)
   myChtObj.Chart.SetSourceData Source:= (appropriate i'th range)
Next

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.http://peltiertech.com/














- Zitierten Text anzeigen -
Click to expand...

Hi Jon,

thank you very much for your great help. I could incorporate your code
snippets. It works.

Thank you.

Regards, Andreas
 
Back
Top