|
???How to set chart position
I would like to generate a chart within the same datasheet at a defined
position. With my limited VB knowledge, the chart is generated at a default position each time Would appreciate expert advice -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* |
???How to set chart position
Here's how to set a chart, "Chart 1", location to the ActiveCell.
Sub MoveMe() With ActiveSheet.Shapes("Chart 1") .Left = ActiveCell.Left .Top = ActiveCell.Top End With End Sub Let me know if you need further help Charles Chickering xl Geek Jaylin wrote: I would like to generate a chart within the same datasheet at a defined position. With my limited VB knowledge, the chart is generated at a default position each time Would appreciate expert advice -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* |
???How to set chart position
Dear Sir
Thank you very much for your fast life saving advice. One major problem I have is that I want the VB to create many charts in the same data sheet. Therefore, the name of the chart will change after each run. I wonder I can I define a dynamic name range after "ActiveSheets.shapes."?????" The following is part of my code Dim r As Integer r = 9 Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets("TOP DMA OB VIETNAM.").Range( _ Sheets("TOP DMA OB VIETNAM.").Cells(r, 8), Sheets("TOP DMA OB VIETNAM.").Cells(r, 20)), PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = "='TOP DMA OB VIETNAM.'!r6c13:r6c25" ActiveChart.Location Whe=xlLocationAsObject, Name:="TOP DMA OB VIETNAM." With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = Sheets("TOP DMA OB VIETNAM.").Cells(r, 5) .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With With ActiveSheet.Shapes(Sheets("TOP DMA OB VIETNAM.").Cells(r, 5)) .Top = Range("C2").Top .Left = Range("C3").Left End With -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* "Jaylin" wrote: I would like to generate a chart within the same datasheet at a defined position. With my limited VB knowledge, the chart is generated at a default position each time Would appreciate expert advice -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* |
???How to set chart position
Loop through you code however many times, moving down CHARTHEIGHT each time:
Dim i as Long Dim cht as Chart Const CHARTHEIGHT As single=100 For i =1 to RequiredCharts Set cht=Charts.add With cht 'Do your chart stuff .Top = Range("C2").Offset(i*CHARTHEIGHT,0).Top .Left = Range("C3").Left End With Next NickHK "Jaylin" wrote in message ... Dear Sir Thank you very much for your fast life saving advice. One major problem I have is that I want the VB to create many charts in the same data sheet. Therefore, the name of the chart will change after each run. I wonder I can I define a dynamic name range after "ActiveSheets.shapes."?????" The following is part of my code Dim r As Integer r = 9 Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets("TOP DMA OB VIETNAM.").Range( _ Sheets("TOP DMA OB VIETNAM.").Cells(r, 8), Sheets("TOP DMA OB VIETNAM.").Cells(r, 20)), PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = "='TOP DMA OB VIETNAM.'!r6c13:r6c25" ActiveChart.Location Whe=xlLocationAsObject, Name:="TOP DMA OB VIETNAM." With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = Sheets("TOP DMA OB VIETNAM.").Cells(r, 5) .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With With ActiveSheet.Shapes(Sheets("TOP DMA OB VIETNAM.").Cells(r, 5)) .Top = Range("C2").Top .Left = Range("C3").Left End With -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* "Jaylin" wrote: I would like to generate a chart within the same datasheet at a defined position. With my limited VB knowledge, the chart is generated at a default position each time Would appreciate expert advice -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* |
???How to set chart position
Thank you very much Mr NickHK I ve tried to test a few code first but get complie error. HOpe you would not mind to futher advice Set cht = Charts.Add With cht ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=...... ActiveChart.SeriesCollection(1).XValues = ....... ActiveChart.Location Whe=xlLocationAsObject, Name...... .Top = Range(a1, a3).Top End With -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* "NickHK" wrote: Loop through you code however many times, moving down CHARTHEIGHT each time: Dim i as Long Dim cht as Chart Const CHARTHEIGHT As single=100 For i =1 to RequiredCharts Set cht=Charts.add With cht 'Do your chart stuff .Top = Range("C2").Offset(i*CHARTHEIGHT,0).Top .Left = Range("C3").Left End With Next NickHK "Jaylin" wrote in message ... Dear Sir Thank you very much for your fast life saving advice. One major problem I have is that I want the VB to create many charts in the same data sheet. Therefore, the name of the chart will change after each run. I wonder I can I define a dynamic name range after "ActiveSheets.shapes."?????" The following is part of my code Dim r As Integer r = 9 Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets("TOP DMA OB VIETNAM.").Range( _ Sheets("TOP DMA OB VIETNAM.").Cells(r, 8), Sheets("TOP DMA OB VIETNAM.").Cells(r, 20)), PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = "='TOP DMA OB VIETNAM.'!r6c13:r6c25" ActiveChart.Location Whe=xlLocationAsObject, Name:="TOP DMA OB VIETNAM." With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = Sheets("TOP DMA OB VIETNAM.").Cells(r, 5) .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With With ActiveSheet.Shapes(Sheets("TOP DMA OB VIETNAM.").Cells(r, 5)) .Top = Range("C2").Top .Left = Range("C3").Left End With -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* "Jaylin" wrote: I would like to generate a chart within the same datasheet at a defined position. With my limited VB knowledge, the chart is generated at a default position each time Would appreciate expert advice -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* |
???How to set chart position
You need to delete all the references to "ActiveChart", as you are working
with "cht" Also, the With cht/End With block should not include the .Top/.left properties, as those do not relate to cht. NickHK "Jaylin" wrote in message ... Thank you very much Mr NickHK I ve tried to test a few code first but get complie error. HOpe you would not mind to futher advice Set cht = Charts.Add With cht ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=...... ActiveChart.SeriesCollection(1).XValues = ....... ActiveChart.Location Whe=xlLocationAsObject, Name...... .Top = Range(a1, a3).Top End With -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* "NickHK" wrote: Loop through you code however many times, moving down CHARTHEIGHT each time: Dim i as Long Dim cht as Chart Const CHARTHEIGHT As single=100 For i =1 to RequiredCharts Set cht=Charts.add With cht 'Do your chart stuff .Top = Range("C2").Offset(i*CHARTHEIGHT,0).Top .Left = Range("C3").Left End With Next NickHK "Jaylin" wrote in message ... Dear Sir Thank you very much for your fast life saving advice. One major problem I have is that I want the VB to create many charts in the same data sheet. Therefore, the name of the chart will change after each run. I wonder I can I define a dynamic name range after "ActiveSheets.shapes."?????" The following is part of my code Dim r As Integer r = 9 Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets("TOP DMA OB VIETNAM.").Range( _ Sheets("TOP DMA OB VIETNAM.").Cells(r, 8), Sheets("TOP DMA OB VIETNAM.").Cells(r, 20)), PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = "='TOP DMA OB VIETNAM.'!r6c13:r6c25" ActiveChart.Location Whe=xlLocationAsObject, Name:="TOP DMA OB VIETNAM." With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = Sheets("TOP DMA OB VIETNAM.").Cells(r, 5) .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With With ActiveSheet.Shapes(Sheets("TOP DMA OB VIETNAM.").Cells(r, 5)) .Top = Range("C2").Top .Left = Range("C3").Left End With -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* "Jaylin" wrote: I would like to generate a chart within the same datasheet at a defined position. With my limited VB knowledge, the chart is generated at a default position each time Would appreciate expert advice -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* |
???How to set chart position
Sorry that I am a slow learner
I do not follow your advice not to include the .Top/.left properties, as those do not relate to cht. Would appreciate advice. Set cht=Charts.add With cht 'Do your chart stuff .Top = Range("C2").Offset(i*CHARTHEIGHT,0).Top .Left = Range("C3").Left End With Next -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* "NickHK" wrote: You need to delete all the references to "ActiveChart", as you are working with "cht" Also, the With cht/End With block should not include the .Top/.left properties, as those do not relate to cht. NickHK "Jaylin" wrote in message ... Thank you very much Mr NickHK I ve tried to test a few code first but get complie error. HOpe you would not mind to futher advice Set cht = Charts.Add With cht ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=...... ActiveChart.SeriesCollection(1).XValues = ....... ActiveChart.Location Whe=xlLocationAsObject, Name...... .Top = Range(a1, a3).Top End With -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* "NickHK" wrote: Loop through you code however many times, moving down CHARTHEIGHT each time: Dim i as Long Dim cht as Chart Const CHARTHEIGHT As single=100 For i =1 to RequiredCharts Set cht=Charts.add With cht 'Do your chart stuff .Top = Range("C2").Offset(i*CHARTHEIGHT,0).Top .Left = Range("C3").Left End With Next NickHK "Jaylin" wrote in message ... Dear Sir Thank you very much for your fast life saving advice. One major problem I have is that I want the VB to create many charts in the same data sheet. Therefore, the name of the chart will change after each run. I wonder I can I define a dynamic name range after "ActiveSheets.shapes."?????" The following is part of my code Dim r As Integer r = 9 Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets("TOP DMA OB VIETNAM.").Range( _ Sheets("TOP DMA OB VIETNAM.").Cells(r, 8), Sheets("TOP DMA OB VIETNAM.").Cells(r, 20)), PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = "='TOP DMA OB VIETNAM.'!r6c13:r6c25" ActiveChart.Location Whe=xlLocationAsObject, Name:="TOP DMA OB VIETNAM." With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = Sheets("TOP DMA OB VIETNAM.").Cells(r, 5) .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With With ActiveSheet.Shapes(Sheets("TOP DMA OB VIETNAM.").Cells(r, 5)) .Top = Range("C2").Top .Left = Range("C3").Left End With -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* "Jaylin" wrote: I would like to generate a chart within the same datasheet at a defined position. With my limited VB knowledge, the chart is generated at a default position each time Would appreciate expert advice -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* |
???How to set chart position
You'll have better results if you use ActiveSheet.ChartObjects.Add instead
of Charts.Add, because it puts the chart directly on the sheet without creating an intermediate chart sheet. The technique is discussed on this web page: http://peltiertech.com/Excel/ChartsH...kChartVBA.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Jaylin" wrote in message ... Dear Sir Thank you very much for your fast life saving advice. One major problem I have is that I want the VB to create many charts in the same data sheet. Therefore, the name of the chart will change after each run. I wonder I can I define a dynamic name range after "ActiveSheets.shapes."?????" The following is part of my code Dim r As Integer r = 9 Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets("TOP DMA OB VIETNAM.").Range( _ Sheets("TOP DMA OB VIETNAM.").Cells(r, 8), Sheets("TOP DMA OB VIETNAM.").Cells(r, 20)), PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = "='TOP DMA OB VIETNAM.'!r6c13:r6c25" ActiveChart.Location Whe=xlLocationAsObject, Name:="TOP DMA OB VIETNAM." With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = Sheets("TOP DMA OB VIETNAM.").Cells(r, 5) .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With With ActiveSheet.Shapes(Sheets("TOP DMA OB VIETNAM.").Cells(r, 5)) .Top = Range("C2").Top .Left = Range("C3").Left End With -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* "Jaylin" wrote: I would like to generate a chart within the same datasheet at a defined position. With my limited VB knowledge, the chart is generated at a default position each time Would appreciate expert advice -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* |
???How to set chart position
Just want to share that I have fixed the problem with the helpful code below
Thanks to this website http://peltiertech.com/Excel/ChartsH...oveAChart.html With ActiveChart.Parent .Height = 325 ' resize .Width = 500 ' resize .Top = 100 ' reposition .Left = 100 ' reposition End With -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* "Jaylin" wrote: Sorry that I am a slow learner I do not follow your advice not to include the .Top/.left properties, as those do not relate to cht. Would appreciate advice. Set cht=Charts.add With cht 'Do your chart stuff .Top = Range("C2").Offset(i*CHARTHEIGHT,0).Top .Left = Range("C3").Left End With Next -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* "NickHK" wrote: You need to delete all the references to "ActiveChart", as you are working with "cht" Also, the With cht/End With block should not include the .Top/.left properties, as those do not relate to cht. NickHK "Jaylin" wrote in message ... Thank you very much Mr NickHK I ve tried to test a few code first but get complie error. HOpe you would not mind to futher advice Set cht = Charts.Add With cht ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=...... ActiveChart.SeriesCollection(1).XValues = ....... ActiveChart.Location Whe=xlLocationAsObject, Name...... .Top = Range(a1, a3).Top End With -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* "NickHK" wrote: Loop through you code however many times, moving down CHARTHEIGHT each time: Dim i as Long Dim cht as Chart Const CHARTHEIGHT As single=100 For i =1 to RequiredCharts Set cht=Charts.add With cht 'Do your chart stuff .Top = Range("C2").Offset(i*CHARTHEIGHT,0).Top .Left = Range("C3").Left End With Next NickHK "Jaylin" wrote in message ... Dear Sir Thank you very much for your fast life saving advice. One major problem I have is that I want the VB to create many charts in the same data sheet. Therefore, the name of the chart will change after each run. I wonder I can I define a dynamic name range after "ActiveSheets.shapes."?????" The following is part of my code Dim r As Integer r = 9 Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets("TOP DMA OB VIETNAM.").Range( _ Sheets("TOP DMA OB VIETNAM.").Cells(r, 8), Sheets("TOP DMA OB VIETNAM.").Cells(r, 20)), PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = "='TOP DMA OB VIETNAM.'!r6c13:r6c25" ActiveChart.Location Whe=xlLocationAsObject, Name:="TOP DMA OB VIETNAM." With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = Sheets("TOP DMA OB VIETNAM.").Cells(r, 5) .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With With ActiveSheet.Shapes(Sheets("TOP DMA OB VIETNAM.").Cells(r, 5)) .Top = Range("C2").Top .Left = Range("C3").Left End With -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* "Jaylin" wrote: I would like to generate a chart within the same datasheet at a defined position. With my limited VB knowledge, the chart is generated at a default position each time Would appreciate expert advice -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* |
???How to set chart position
Thank you very much Mr Peltier
I have visited your website many times to solve my chart problem. It is great!!! -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* "Jon Peltier" wrote: You'll have better results if you use ActiveSheet.ChartObjects.Add instead of Charts.Add, because it puts the chart directly on the sheet without creating an intermediate chart sheet. The technique is discussed on this web page: http://peltiertech.com/Excel/ChartsH...kChartVBA.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Jaylin" wrote in message ... Dear Sir Thank you very much for your fast life saving advice. One major problem I have is that I want the VB to create many charts in the same data sheet. Therefore, the name of the chart will change after each run. I wonder I can I define a dynamic name range after "ActiveSheets.shapes."?????" The following is part of my code Dim r As Integer r = 9 Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets("TOP DMA OB VIETNAM.").Range( _ Sheets("TOP DMA OB VIETNAM.").Cells(r, 8), Sheets("TOP DMA OB VIETNAM.").Cells(r, 20)), PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = "='TOP DMA OB VIETNAM.'!r6c13:r6c25" ActiveChart.Location Whe=xlLocationAsObject, Name:="TOP DMA OB VIETNAM." With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = Sheets("TOP DMA OB VIETNAM.").Cells(r, 5) .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With With ActiveSheet.Shapes(Sheets("TOP DMA OB VIETNAM.").Cells(r, 5)) .Top = Range("C2").Top .Left = Range("C3").Left End With -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* "Jaylin" wrote: I would like to generate a chart within the same datasheet at a defined position. With my limited VB knowledge, the chart is generated at a default position each time Would appreciate expert advice -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* |
| All times are GMT +1. The time now is 11:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com