Macro and chart help

[millwalll]

Hi all ,
Need some help I have recorded a macro that produces a chart. When the user
try's another search the chart goes wrong. So I need to delte the chart if
the user does another search. That way there wont be a chart on screen that
is wrong any can i do this by using if statement or is there another way to
do this?

Thanks

 

[JLGWhiz]

It helps to see the code you are working with.

 

[millwalll]

this the code for the chat
Sub TotalText()
'
' TotalText Macro
'

'
Range("A1:M30").Select
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add
Key:=Range("C2:C30"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort
.SetRange Range("A1:M30")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(10), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Range("C11,J11,C20,J20,C24,J24,C28,J28,C31,J31,C36,J36").Select
Range("J36").Activate
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range( _

"Data!$C$11,Data!$J$11,Data!$C$20,Data!$J$20,Data!$C$24,Data!$J$24,Data!$C$28,Data!$J$28,Data!$C$31,Data!$J$31,Data!$C$36,Data!$J$36" _
)
ActiveChart.ChartType = xlColumnClustered
ActiveChart.ChartStyle = 44
ActiveChart.ClearToMatchStyle
ActiveChart.Legend.Select
Selection.Delete
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartTitle.Text = "Network Text's"
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
End Sub

 

[JLGWhiz]

The way your code is written, the chart is constantly tied to the source
range and any change in the source range will change the chart. Some people
want this feature so their charts are always up to the minute.

To separate the chart from the source range, you can copy the data from the
source range to a different sheet that is not used by the general public and
use that to create the chart.

This would be the data you want to copy to the separate sheet:

Range("C11,J11,C20,J20,C24,J24,C28,J28,C31,J31,C36,J36")

By copying the data and then creating the chart, your chart will remain
unchanged until you run your macro to create it again. Of course it would
be wiser to write a new macro that simply updates the original rather than
continue to create each time, since the create method would generate error
flags due to the existing chart.
But the point is, unless you separate the chart from its source data and
others are using the source file, then you will continue to have the
potential for unwanted changes to your chart.

 

[millwalll]

is there anyway i can have the chat display for like 10 min then delete itself

 

[JLGWhiz]

Why not just print it and then delete it?

is there anyway i can have the chat display for like 10 min then delete itself

 

[JLGWhiz]

I think you need to re-post in the Chart group to get more knowledgeable
information. I have limited experience with charts created and controlled
via VBA.
I usually create my charts manually and then just update them through code.
Someone in the Chart group can probably give you same good advice on this.

 

[millwalll]

thanks one more question I trying to produce a chat and I get error saying
series format too long what does this mean ?

 

[Jon Peltier]

This was asked and answered in the charting group.

- Jon