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]

sorry, I was wrong on the above code, the problem is on error goto 0 reset err code to 0.

the correct code are:

dim cht as chart

on error resume next

set cht = activeworkbook.chart("YourChartNameHere") 'your actual chart name

if err = 0 then
cht.delete
end if

cht.add

your rest code here...

HTH
lantiger

 

[Morrissey]

thank you it's good that someone can help instead of getting into a moral debate

 

[lantiger]

blame it on the stupid lunch I had today

anyway, the above code works, but not perfect. here we go again

dim cht as chart

on error resume next

set cht = activeworkbook.chart("YourChartNameHere") 'your actual chart name

if err = 0 then
application.DisplayAlerts = False
cht.delete
application.DisplayAlerts = true
end if

on error goto 0

cht.add

your code...

HTH
lantiger

 

[Morrissey]

thanks I got it working now and for Mark yes I will be reading the code seeing how it executes the operations and dsorry if am here is a future apology if I become mentally challenged in the future over Excel.

 

[lantiger]

where is my chocolate medallion?

glad you got the code working.

lantiger

 

[Mark O'Brien]

On 2002-04-11 11:48, Morrissey wrote:
thanks I got it working now and for Mark yes I will be reading the code seeing how it executes the operations and dsorry if am here is a future apology if I become mentally challenged in the future over Excel.

Is that my name being mentioned in this thread? I haven't contributed anything to it, apart from this post.

Quick word of advice though (and at 16/17 I didn't know this either), it doesn't matter what your teacher can or cannot teach you when there are literally hundreds of books on the subject out there in circulation.

I used to work for a small company (I was a consultant engineer and a secretary for a time) and we didn't have cash to splash on books let alone training. Let me tell you this, it didn't take me 6 weeks to learn the basics of VBA by going to the library across the road.

Anyway, seriously, good luck with your A Level.

 

[Morrissey]

Thank you for your support about my A level and I am learning VB during my Summer Vacation in preperation for the Access project and I could learn VBA in 6 weeks however I have 3 other subjects where I must learn facts aswell