Chart count????

[pdbowling]

Hi, this is actually an Excel question but I believe a great deal of the programming is the same as Access..... I'm generating a graph in a spreadsheet. (using the handy dandy record macro function... it writes code for me...) The graph must be resized to fit the format of the page and when it is selected, the reference is ("Chart 1") or ("Chart 2")... or something like that. Each time I test my code.... the number increments and crashes the macro. sooooo can someone tell how to either...
1) Generate the graph with handwritten code or
2) reset the chart count at the beginning of the macro.
Just let me know if you need more info or if the code snippet is not helpful
---------------------------------------
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/3/2003 by pabowlin
'

'
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("A1:M4"), PlotBy:= _
xlRows
ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "PC Team Members Trained"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

'here is where I manipulate the chart to fit the page

ActiveSheet.Shapes("Chart 4").IncrementLeft 158.25
ActiveSheet.Shapes("Chart 4").IncrementTop -104.25
ActiveSheet.Shapes("Chart 4").ScaleWidth 0.65, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 4").ScaleHeight 0.51, msoFalse, msoScaleFromTopLeft

End Sub

 

[FoFa]

Not being an excel wiz at all (why, I use Access) but I would say your Chart.Add is causing the problem. It would be creating a new chart each time. If there is one already there, than it would have to increment the name to make it unique.
But I am guessing. Can you just add a chart one time, and change the rows/columns it obtains the data from instead?

 

[pdbowling]

Thanks all

I got redirected to the Excel specific forum. I didn't know there was one here. Anyway, the chart.Add is the only way I know to generate a chart and I also added new code to delete all old charts before the .add and it still keeps the old count??? I appreciate your response. I'll go ahead and end this thread though. Thanx
PB