Chart runtime error 1004 please help

[ryan.fitzpatrick3]

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("E322322,E332332"), 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("E323323,E333333"), 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

 

[Joel]

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("E322322,E332332"), _
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("E323323,E333333"), _
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

 

[Jon Peltier]

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/ChartsHowTo/QuickChartVBA.html

- Jon