Help With VBA Making A Chart Last Bit Of Help I Need Please!

[Morrissey]

Hello this is one of my final problems and I need help with it ASAP infact the first person to solve it gets a chocolate medallion

Ok I Have A Macro assigned to a button that makes a chart all works well however I want the chart to be made onto the same worksheet all the time (which is called "Chart") but when I run it again I have a debug error and the chart is made onto another worksheet called worksheet2 (the next time it goes worksheet 3 etc.)
What I want is for each time that button is pressed the chart goes onto the chart worksheet but overwrites the other chart please help me here is the VB code

Sub MakeChart()
'
' MakeChart Macro
' Makes A Chart From The Data
'

'
Range("A2:A11,M2:M11").Select
Range("M2").Activate
Charts.Add
ActiveChart.ChartType = xlLineStacked
ActiveChart.SetSourceData Source:=Sheets("Database").Range("A2:A11,M2:M11"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Revenue"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Dates"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Sales"
End With
End Sub

[lantiger]

Using chart.add will always add a new chart. If you know the name of the chart, as you has suggested, you can always delete the old chart and draw the new chart.

Example:

dim cht as chart
on error resume next
set cht = ActiveWorkbook.Chart.add("YourChartNameHere")
on error goto 0

if err = 0 then
cht.delete 'This delete the old chart
end if



Your normal code here...

[Morrissey]

where exactly do I put this in my code then the chocolate medallion is yours!

[Morrissey]

i need this

[lantiger]

Hey Morrissey:
Sorry I was not clear.
Just in the same sub you have, right before your chart.add function. all it does is check if the chart already exist or not, if it does, it just delete it, so you can add the new one .
if you need more help, just lmk.

HTH
lantiger

[Morrissey]

One More Problem but we are nearly there I have inserted the code but another debug message comes up saying

Object variable with block not set' and the following code is highlighted

cht.Delete 'This delete the old chart

here is all the code

Sub MakeChart()
'
' MakeChart Macro
' Makes A Chart From The Data
'

'
Range("A2:A11,M2:M11").Select
Range("M2").Activate
Dim cht As Chart
On Error Resume Next
Set cht = ActiveWorkbook.Chart.Add("YourChartNameHere")
On Error GoTo 0

If Err = 0 Then
cht.Delete
End If
Charts.Add
ActiveChart.ChartType = xlLineStacked
ActiveChart.SetSourceData Source:=Sheets("Database").Range("A2:A11,M2:M11"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Revenue"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Dates"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Sales"
End With
End Sub

HELP NEEDED QUICK!!!!

[Morrissey]

please!!!

[Steve Hartman]

How many "one last problem" does this make for you in the last six weeks now?

This is the problem with giving a man a fish instead of teaching him how to fish.

Of course the man must want to learn instead of wanting someone else do his work for him.

Goodbye Arash.

[ This Message was edited by: Steve Hartman on 2002-04-11 11:16 ]

[Morrissey]

I'm not here for a debate on whether I should learn VBA (not a requirement of the course)or not I wish to know this problem ok this board is for helping people not getting into an ethical debate if you can't help or don't want to help no one asked ok mind your own business!

[Morrissey]

Also I don't just insert this code and leave it alone and and take the credit for it actually to try and learn the code however I can't in 6 weeks and teach a man to fish? yes well who will teach me how MANY TIMES DO I HAVE TO SAY THIS MY TEACHER DOES NOT KNOW VBA!!!!<------- If ther is a way I could learn VBA in 6 weeks and do everything else then I would but I can't so mind your own business!