Making a Chart with VBA

  • Thread starter ryan.fitzpatrick3
  • Start date
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("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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top