Chart numbering frustration in VBA

[nutwood33]

I'm creating a simple little macro that draws a chart from some data in Excel. The frustration comes because every time my macro draws a chart, it sequences the number. The first chart is "chart 1" the second is "chart 2" even if "chart 1" no longer exists.
I know there's a simple answer to this problem but I can't find it, hence the frustration. Please could someone tell me how to take charge of the chart identification before I chew another corner off my keyboard!
Steve

[mnemtsas]

How are you adding the chart? If you are using the Charts.Add method then you can use this to get a reference to the new chart object.

dim chartNew as Chart

  set chartNew=ActiveWorkBook.Charts.Add

  chartNew.ChartTitle.Characters.Text = "This is a New Chart"

And so on.

By the way your CD's were put in the mail today.

[nutwood33]

Hello again Mark, I'm adding my chart as per below. This is a simplified version of what I'm doing (with your code added). The point is to graph a couple of columns and only select the cells with data in. This works fine but each time it runs it creates a new chart on top of the previous whereas I want to replace the previous chart with the new one. I thought I'd simply be able to put a line of code at the start to delete "chart 1" but that doesn't work as the next time around it's called "chart 2"!
I've added your code as per below but there's something wrong with how I've done it as it won't add the title.
Assuming I can get it to add the title will I now be able to add a line to delete "This is a new chart" at the start of the sub?
Cheers steve

Sub graphtest()
Dim chartNew As Chart

Do
rowpointer = rowpointer + 1
locate = Cells(rowpointer, 1).Value
Loop Until locate = 0
c = rowpointer - 1
Set myrange = Worksheets("Sheet1").Range(Cells(1, 1), Cells(c, 2))
Set chartNew = ActiveWorkbook.Charts.Add
chartNew.ChartType = xlXYScatterSmooth
chartNew.SetSourceData Source:=myrange, PlotBy:=xlColumns
chartNew.Location Where:=xlLocationAsObject, Name:="Sheet1"
With chartNew
.HasTitle = True
.ChartTitle.Characters.Text = "This is a New Chart"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Shrinkage %"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "M/C %"
End With

End Sub

How are you adding the chart? If you are using the Charts.Add method then you can use this to get a reference to the new chart object.

dim chartNew as Chart

  set chartNew=ActiveWorkBook.Charts.Add

  chartNew.ChartTitle.Characters.Text = "This is a New Chart"

And so on.

By the way your CD's were put in the mail today.

[mnemtsas]

Hi Steve,

Try:

Sub graphtest()
Dim chartNew As Chart
Dim chartsTemp As ChartObjects

  On Error GoTo errorHandler 'put in error handling to handle if no charts exist
  
  '
  'Deletes the last created chart in the worksheet
  '
  Set chartsTemp = ActiveSheet.ChartObjects
  If chartsTemp.Count > 0 Then
    chartsTemp(chartsTemp.Count).Delete
  End If
  Do
    rowpointer = rowpointer + 1
    locate = Cells(rowpointer, 1).Value
  Loop Until locate = 0
  c = rowpointer - 1
  Set myrange = Worksheets("Sheet1").Range(Cells(1, 1), Cells(c, 2))
  Set chartNew = ActiveWorkbook.Charts.Add
  chartNew.ChartType = xlXYScatterSmooth
  chartNew.SetSourceData Source:=myrange, PlotBy:=xlColumns
  chartNew.Location Where:=xlLocationAsObject, Name:="Sheet1"
  '
  'FOr some reason VBA loses the handle on the chart when you change the location so to workaround this
  'just re-reference it here
  '
  Set chartNew = Application.ActiveChart
  With Application.ActiveChart
    .HasTitle = True
    '
    'Sorry I got the chart title stuff wrong :-(
    '
    .ChartTitle.Text = "This is a New Chart"
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Shrinkage %"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "M/C %"
  End With
  
  Exit Sub
errorHandler:
  If Err.Number = 91 Then 'handles the case when there is no chart
    Err.Clear
    Resume Next
  Else
    MsgBox "Unhandled Error" & vbCrLf & Err.Number & " " & Err.Description
  End If
End Sub

[Tonye53]

Rather than refer to the chart by the chart name, refer to it by its index number on the active sheet. if it is the only chart on the sheet you can refer to it by:

Activesheet("sheet name").drawingobjects(1)

Hope this helps !