R
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.
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
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.
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