Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create several charts in one go using VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create several charts in one go using VBA
On Aug 5, 11:06*am, andreashermle wrote:
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 "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." |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create several charts in one go using VBA
On Aug 5, 8:51*pm, Don Guillett Excel MVP
wrote: On Aug 5, 11:06*am, andreashermle wrote: 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 "If desired, send your file to dguillett 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 - Hi Don, what a service! Will do the requested shorty. Thank you. Regards, Andreas |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create several charts in one go using VBA
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/ On 8/5/2010 12:06 PM, andreashermle wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create several charts in one go using VBA
On 10 Aug., 18:23, Jon Peltier wrote:
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/ On 8/5/2010 12:06 PM, andreashermle wrote: 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- Zitierten Text ausblenden - - Zitierten Text anzeigen - Hi Jon, thank you very much for your great help. I could incorporate your code snippets. It works. Thank you. Regards, Andreas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to click on charts & unable to create any new charts Excel | Charts and Charting in Excel | |||
how to create 0 to 0 value charts | Charts and Charting in Excel | |||
please help... how to create org charts via VBA... | Charts and Charting in Excel | |||
how do i create 4 pie charts | Charts and Charting in Excel | |||
Best way to create 27 charts with vba? | Excel Programming |