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

Morrissey

Board Regular
Joined
Mar 8, 2002
Messages
85
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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...
 
Upvote 0
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
 
Upvote 0
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!!!!
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top