I have 2 charts on sheet1 named (position sheet) they are centered at
the bottom. I have this code inwhich I found here. What I'm trying to
do is 1) create 2 charts one for white flour and the other for wheat
flour 2) they are linked to buttons so when I push the button it puts
the chart on the sheet. 3) I want the same position on the sheet every
time I click the button 4) if I click the button a few times I don't
want the charts to constantly layer on itself, so this is my code. It
works good then when I save it and reenter it gives me a run-time
error 1004 like. Why did it work and now it doesn't work. Can anyone
help me fix this code where if I click this button 1 times or 100
times it'll be the same even if I save and exit and come back in or
can they give me code where the two charts will populate onto the page
in the same place everytime with code that will work until I save and
exit then won't work?

Sub US_Flour_Volumes()

Worksheets("Position Sheet").ChartObjects.Delete
Application.ScreenUpdating = False
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:="Position
Sheet"
With ActiveChart
.SetSourceData
Source:=Sheets("Data").Range("E322:P322,E332:P332"), PlotBy:= _
xlRows
.HasTitle = True
.ChartType = xlColumnClustered
.HasLegend = False
.SeriesCollection(1).XValues = "=Data!R321C5:R321C16"
.SeriesCollection(1).Name = "=""2006"""
.SeriesCollection(2).Name = "=""2007"""
.ChartTitle.Characters.Text = "US White Flour Volumes"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.ChartTitle.Select
Selection.Left = 105
Selection.Top = 6
ActiveChart.ChartArea.Select

Application.ScreenUpdating = False
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:="Position
Sheet"
With ActiveChart
.SetSourceData
Source:=Sheets("Data").Range("E323:P323,E333:P333"), PlotBy:= _
xlRows
.HasTitle = True
.ChartType = xlColumnClustered
.HasLegend = False
.SeriesCollection(1).XValues = "=Data!R321C5:R321C16"
.SeriesCollection(1).Name = "=""2006"""
.SeriesCollection(2).Name = "=""2007"""
.ChartTitle.Characters.Text = "US Wheat Flour Volumes"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
Range("A1").Select

Dim iChart As Long
Dim nCharts As Long
Dim dTop As Double
Dim dLeft As Double
Dim dHeight As Double
Dim dWidth As Double
Dim nColumns As Long

dTop = 500 ' top of first row of charts
dLeft = 90 ' left of first column of charts
dHeight = 200 ' height of all charts
dWidth = 250 ' width of all charts
nColumns = 3 ' number of columns of charts
nCharts = ActiveSheet.ChartObjects.Count

For iChart = 1 To nCharts
With ActiveSheet.ChartObjects(iChart)
.Height = dHeight
.Width = dWidth
.Top = dTop + Int((iChart - 1) / nColumns) * dHeight
.Left = dLeft + ((iChart - 1) Mod nColumns) * dWidth
End With
Next

End Sub

I always struggle with charts. There is a bug in the Macro Recording of
charts. the don't run. Ifixed your code so it know runs. These secrets
took me days (maybe weeks) to figure out on my own by trial an error.

When a chart get added it is on its own sheet (a chart object). The
location method moves the chart onto a worksheet. For some reason the name
of the chart get slightly changed. the only method I found of getting it to
work is using the SET when the chart is added. I like using names of charts
rather than active chart. See code below.

Sub US_Flour_Volumes()


For Each chrt In Worksheets("Position Sheet").ChartObjects
chrt.Delete
Next chrt

Application.ScreenUpdating = False
Set FlourChart = Charts.Add

FlourChart.Location _
Where:=xlLocationAsObject, _
Name:="Position Sheet"
Set FlourChart = ActiveChart
With FlourChart
.SetSourceData _
Source:=Sheets("Data").Range("E322:P322,E332:P332"), _
PlotBy:=xlRows
.HasTitle = True
.ChartType = xlColumnClustered
.HasLegend = False
.SeriesCollection(1).XValues = "=Data!R321C5:R321C16"
.SeriesCollection(1).Name = "=""2006"""
.SeriesCollection(2).Name = "=""2007"""
.ChartTitle.Characters.Text = "US White Flour Volumes"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

FlourChart.HasLegend = True
FlourChart.Legend.Position = xlBottom
With FlourChart.ChartTitle
.Left = 105
.Top = 6
End With
FlourChart.ChartArea.Select

Application.ScreenUpdating = False
Set FlourChart = Charts.Add
FlourChart.Location _
Where:=xlLocationAsObject, _
Name:="Position Sheet"
Set FlourChart = ActiveChart
With FlourChart
.SetSourceData _
Source:=Sheets("Data"). _
Range("E323:P323,E333:P333"), _
PlotBy:=xlRows
.HasTitle = True
.ChartType = xlColumnClustered
.HasLegend = False
.SeriesCollection(1).XValues = "=Data!R321C5:R321C16"
.SeriesCollection(1).Name = "=""2006"""
.SeriesCollection(2).Name = "=""2007"""
.ChartTitle.Characters.Text = "US Wheat Flour Volumes"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

FlourChart.HasLegend = True
FlourChart.Legend.Select
Selection.Position = xlBottom
Range("A1").Select

Dim iChart As Long
Dim nCharts As Long
Dim dTop As Double
Dim dLeft As Double
Dim dHeight As Double
Dim dWidth As Double
Dim nColumns As Long

dTop = 500 ' top of first row of charts
dLeft = 90 ' left of first column of charts
dHeight = 200 ' height of all charts
dWidth = 250 ' width of all charts
nColumns = 3 ' number of columns of charts
nCharts = ActiveSheet.ChartObjects.Count

For iChart = 1 To nCharts
With ActiveSheet.ChartObjects(iChart)
.Height = dHeight
.Width = dWidth
.Top = dTop + Int((iChart - 1) / nColumns) * dHeight
.Left = dLeft + ((iChart - 1) Mod nColumns) * dWidth
End With
Next

End Sub


"(E-Mail Removed)" wrote:

> I have 2 charts on sheet1 named (position sheet) they are centered at
> the bottom. I have this code inwhich I found here. What I'm trying to
> do is 1) create 2 charts one for white flour and the other for wheat
> flour 2) they are linked to buttons so when I push the button it puts
> the chart on the sheet. 3) I want the same position on the sheet every
> time I click the button 4) if I click the button a few times I don't
> want the charts to constantly layer on itself, so this is my code. It
> works good then when I save it and reenter it gives me a run-time
> error 1004 like. Why did it work and now it doesn't work. Can anyone
> help me fix this code where if I click this button 1 times or 100
> times it'll be the same even if I save and exit and come back in or
> can they give me code where the two charts will populate onto the page
> in the same place everytime with code that will work until I save and
> exit then won't work?
>
> Sub US_Flour_Volumes()
>
> Worksheets("Position Sheet").ChartObjects.Delete
> Application.ScreenUpdating = False
> Charts.Add
> ActiveChart.Location Where:=xlLocationAsObject, Name:="Position
> Sheet"
> With ActiveChart
> .SetSourceData
> Source:=Sheets("Data").Range("E322:P322,E332:P332"), PlotBy:= _
> xlRows
> .HasTitle = True
> .ChartType = xlColumnClustered
> .HasLegend = False
> .SeriesCollection(1).XValues = "=Data!R321C5:R321C16"
> .SeriesCollection(1).Name = "=""2006"""
> .SeriesCollection(2).Name = "=""2007"""
> .ChartTitle.Characters.Text = "US White Flour Volumes"
> .Axes(xlCategory, xlPrimary).HasTitle = False
> .Axes(xlValue, xlPrimary).HasTitle = False
> End With
>
> ActiveChart.HasLegend = True
> ActiveChart.Legend.Select
> Selection.Position = xlBottom
> ActiveChart.ChartTitle.Select
> Selection.Left = 105
> Selection.Top = 6
> ActiveChart.ChartArea.Select
>
> Application.ScreenUpdating = False
> Charts.Add
> ActiveChart.Location Where:=xlLocationAsObject, Name:="Position
> Sheet"
> With ActiveChart
> .SetSourceData
> Source:=Sheets("Data").Range("E323:P323,E333:P333"), PlotBy:= _
> xlRows
> .HasTitle = True
> .ChartType = xlColumnClustered
> .HasLegend = False
> .SeriesCollection(1).XValues = "=Data!R321C5:R321C16"
> .SeriesCollection(1).Name = "=""2006"""
> .SeriesCollection(2).Name = "=""2007"""
> .ChartTitle.Characters.Text = "US Wheat Flour Volumes"
> .Axes(xlCategory, xlPrimary).HasTitle = False
> .Axes(xlValue, xlPrimary).HasTitle = False
> End With
>
> ActiveChart.HasLegend = True
> ActiveChart.Legend.Select
> Selection.Position = xlBottom
> Range("A1").Select
>
> Dim iChart As Long
> Dim nCharts As Long
> Dim dTop As Double
> Dim dLeft As Double
> Dim dHeight As Double
> Dim dWidth As Double
> Dim nColumns As Long
>
> dTop = 500 ' top of first row of charts
> dLeft = 90 ' left of first column of charts
> dHeight = 200 ' height of all charts
> dWidth = 250 ' width of all charts
> nColumns = 3 ' number of columns of charts
> nCharts = ActiveSheet.ChartObjects.Count
>
> For iChart = 1 To nCharts
> With ActiveSheet.ChartObjects(iChart)
> .Height = dHeight
> .Width = dWidth
> .Top = dTop + Int((iChart - 1) / nColumns) * dHeight
> .Left = dLeft + ((iChart - 1) Mod nColumns) * dWidth
> End With
> Next
>
> End Sub
>
>

You could also use

Set Chart = ActiveSheet.ChartObjects.Add(90, 500, 250, 200).Chart

which directly adds the chart to the sheet without the intermediate chart
sheet. I discuss this and other chart programming tips here:

http://peltiertech.com/Excel/ChartsH...kChartVBA.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Joel" <(E-Mail Removed)> wrote in message
news:42A18612-FC6D-4F01-9B3A-(E-Mail Removed)...
>I always struggle with charts. There is a bug in the Macro Recording of
> charts. the don't run. Ifixed your code so it know runs. These secrets
> took me days (maybe weeks) to figure out on my own by trial an error.
>
> When a chart get added it is on its own sheet (a chart object). The
> location method moves the chart onto a worksheet. For some reason the
> name
> of the chart get slightly changed. the only method I found of getting it
> to
> work is using the SET when the chart is added. I like using names of
> charts
> rather than active chart. See code below.
>
> Sub US_Flour_Volumes()
>
>
> For Each chrt In Worksheets("Position Sheet").ChartObjects
> chrt.Delete
> Next chrt
>
> Application.ScreenUpdating = False
> Set FlourChart = Charts.Add
>
> FlourChart.Location _
> Where:=xlLocationAsObject, _
> Name:="Position Sheet"
> Set FlourChart = ActiveChart
> With FlourChart
> .SetSourceData _
> Source:=Sheets("Data").Range("E322:P322,E332:P332"), _
> PlotBy:=xlRows
> .HasTitle = True
> .ChartType = xlColumnClustered
> .HasLegend = False
> .SeriesCollection(1).XValues = "=Data!R321C5:R321C16"
> .SeriesCollection(1).Name = "=""2006"""
> .SeriesCollection(2).Name = "=""2007"""
> .ChartTitle.Characters.Text = "US White Flour Volumes"
> .Axes(xlCategory, xlPrimary).HasTitle = False
> .Axes(xlValue, xlPrimary).HasTitle = False
> End With
>
> FlourChart.HasLegend = True
> FlourChart.Legend.Position = xlBottom
> With FlourChart.ChartTitle
> .Left = 105
> .Top = 6
> End With
> FlourChart.ChartArea.Select
>
> Application.ScreenUpdating = False
> Set FlourChart = Charts.Add
> FlourChart.Location _
> Where:=xlLocationAsObject, _
> Name:="Position Sheet"
> Set FlourChart = ActiveChart
> With FlourChart
> .SetSourceData _
> Source:=Sheets("Data"). _
> Range("E323:P323,E333:P333"), _
> PlotBy:=xlRows
> .HasTitle = True
> .ChartType = xlColumnClustered
> .HasLegend = False
> .SeriesCollection(1).XValues = "=Data!R321C5:R321C16"
> .SeriesCollection(1).Name = "=""2006"""
> .SeriesCollection(2).Name = "=""2007"""
> .ChartTitle.Characters.Text = "US Wheat Flour Volumes"
> .Axes(xlCategory, xlPrimary).HasTitle = False
> .Axes(xlValue, xlPrimary).HasTitle = False
> End With
>
> FlourChart.HasLegend = True
> FlourChart.Legend.Select
> Selection.Position = xlBottom
> Range("A1").Select
>
> Dim iChart As Long
> Dim nCharts As Long
> Dim dTop As Double
> Dim dLeft As Double
> Dim dHeight As Double
> Dim dWidth As Double
> Dim nColumns As Long
>
> dTop = 500 ' top of first row of charts
> dLeft = 90 ' left of first column of charts
> dHeight = 200 ' height of all charts
> dWidth = 250 ' width of all charts
> nColumns = 3 ' number of columns of charts
> nCharts = ActiveSheet.ChartObjects.Count
>
> For iChart = 1 To nCharts
> With ActiveSheet.ChartObjects(iChart)
> .Height = dHeight
> .Width = dWidth
> .Top = dTop + Int((iChart - 1) / nColumns) * dHeight
> .Left = dLeft + ((iChart - 1) Mod nColumns) * dWidth
> End With
> Next
>
> End Sub
>
>
> "(E-Mail Removed)" wrote:
>
>> I have 2 charts on sheet1 named (position sheet) they are centered at
>> the bottom. I have this code inwhich I found here. What I'm trying to
>> do is 1) create 2 charts one for white flour and the other for wheat
>> flour 2) they are linked to buttons so when I push the button it puts
>> the chart on the sheet. 3) I want the same position on the sheet every
>> time I click the button 4) if I click the button a few times I don't
>> want the charts to constantly layer on itself, so this is my code. It
>> works good then when I save it and reenter it gives me a run-time
>> error 1004 like. Why did it work and now it doesn't work. Can anyone
>> help me fix this code where if I click this button 1 times or 100
>> times it'll be the same even if I save and exit and come back in or
>> can they give me code where the two charts will populate onto the page
>> in the same place everytime with code that will work until I save and
>> exit then won't work?
>>
>> Sub US_Flour_Volumes()
>>
>> Worksheets("Position Sheet").ChartObjects.Delete
>> Application.ScreenUpdating = False
>> Charts.Add
>> ActiveChart.Location Where:=xlLocationAsObject, Name:="Position
>> Sheet"
>> With ActiveChart
>> .SetSourceData
>> Source:=Sheets("Data").Range("E322:P322,E332:P332"), PlotBy:= _
>> xlRows
>> .HasTitle = True
>> .ChartType = xlColumnClustered
>> .HasLegend = False
>> .SeriesCollection(1).XValues = "=Data!R321C5:R321C16"
>> .SeriesCollection(1).Name = "=""2006"""
>> .SeriesCollection(2).Name = "=""2007"""
>> .ChartTitle.Characters.Text = "US White Flour Volumes"
>> .Axes(xlCategory, xlPrimary).HasTitle = False
>> .Axes(xlValue, xlPrimary).HasTitle = False
>> End With
>>
>> ActiveChart.HasLegend = True
>> ActiveChart.Legend.Select
>> Selection.Position = xlBottom
>> ActiveChart.ChartTitle.Select
>> Selection.Left = 105
>> Selection.Top = 6
>> ActiveChart.ChartArea.Select
>>
>> Application.ScreenUpdating = False
>> Charts.Add
>> ActiveChart.Location Where:=xlLocationAsObject, Name:="Position
>> Sheet"
>> With ActiveChart
>> .SetSourceData
>> Source:=Sheets("Data").Range("E323:P323,E333:P333"), PlotBy:= _
>> xlRows
>> .HasTitle = True
>> .ChartType = xlColumnClustered
>> .HasLegend = False
>> .SeriesCollection(1).XValues = "=Data!R321C5:R321C16"
>> .SeriesCollection(1).Name = "=""2006"""
>> .SeriesCollection(2).Name = "=""2007"""
>> .ChartTitle.Characters.Text = "US Wheat Flour Volumes"
>> .Axes(xlCategory, xlPrimary).HasTitle = False
>> .Axes(xlValue, xlPrimary).HasTitle = False
>> End With
>>
>> ActiveChart.HasLegend = True
>> ActiveChart.Legend.Select
>> Selection.Position = xlBottom
>> Range("A1").Select
>>
>> Dim iChart As Long
>> Dim nCharts As Long
>> Dim dTop As Double
>> Dim dLeft As Double
>> Dim dHeight As Double
>> Dim dWidth As Double
>> Dim nColumns As Long
>>
>> dTop = 500 ' top of first row of charts
>> dLeft = 90 ' left of first column of charts
>> dHeight = 200 ' height of all charts
>> dWidth = 250 ' width of all charts
>> nColumns = 3 ' number of columns of charts
>> nCharts = ActiveSheet.ChartObjects.Count
>>
>> For iChart = 1 To nCharts
>> With ActiveSheet.ChartObjects(iChart)
>> .Height = dHeight
>> .Width = dWidth
>> .Top = dTop + Int((iChart - 1) / nColumns) * dHeight
>> .Left = dLeft + ((iChart - 1) Mod nColumns) * dWidth
>> End With
>> Next
>>
>> End Sub
>>
>>