Error when adding title to chart

[nbuk]

I am creating several charts and I get an error when I try to add a title to some graphs.

        'Graph
        With Sheet1.ChartObjects.Add(Left:=390, Width:=375, Top:=10, Height:=225)
        .Chart.SetSourceData Source:=Sheet22.Range("A1:R2")
        .Chart.ChartType = xlColumnClustered
        .Chart.ChartTitle.Characters.Text = "SC1 Downtime " & StartDate & "-" & EndDate
        .Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Total Hours"
        End With
        
        With Sheet1.ChartObjects.Add(Left:=775, Width:=375, Top:=10, Height:=225)
        .Chart.SetSourceData Source:=Sheet22.Range("A3:R4")
        .Chart.ChartType = xlColumnClustered
        .Chart.ChartTitle.Characters.Text = "SC2 Downtime " & StartDate & "-" & EndDate
        .Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Total Hours"
        End With
        
        With Sheet1.ChartObjects.Add(Left:=390, Width:=375, Top:=245, Height:=225)
        .Chart.SetSourceData Source:=Sheet22.Range("A5:R6")
        .Chart.ChartType = xlColumnClustered
        .Chart.ChartTitle.Characters.Text = "SC5 Downtime " & StartDate & "-" & EndDate
        .Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Total Hours"
        End With
        
        With Sheet1.ChartObjects.Add(Left:=775, Width:=375, Top:=245, Height:=225)
        .Chart.SetSourceData Source:=Sheet22.Range("A7:R8")
        .Chart.ChartType = xlColumnClustered
        .Chart.ChartTitle.Characters.Text = "SC4 Downtime " & StartDate & "-" & EndDate
        .Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Total Hours"
        End With
        
        'Graph Line
        With Sheet1.ChartObjects.Add(Left:=390, Width:=375, Top:=480, Height:=225)
        .Chart.SetSourceData Source:=Sheet8.Range("T" & iStart & ":T" & iEnd)
        .Chart.ChartType = xlLineMarkers
        .Chart.ChartTitle.Characters.Text = "SC1 " & StartDate & "-" & EndDate
        .Chart.SeriesCollection(1).XValues = Sheet8.Range("B" & iStart & ":B" & iEnd)
        .Chart.Axes(xlCategory).CategoryType = xlCategoryScale
        .Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Inch2/min"
        End With
        
        With Sheet1.ChartObjects.Add(Left:=775, Width:=375, Top:=480, Height:=225)
        .Chart.SetSourceData Source:=Sheet9.Range("T" & iStart & ":T" & iEnd)
        .Chart.ChartType = xlLineMarkers
        .Chart.ChartTitle.Characters.Text = "SC2 " & StartDate & "-" & EndDate
        .Chart.SeriesCollection(1).XValues = Sheet9.Range("B" & iStart & ":B" & iEnd)
        .Chart.Axes(xlCategory).CategoryType = xlCategoryScale
        .Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Inch2/min"
        End With
        
        With Sheet1.ChartObjects.Add(Left:=390, Width:=375, Top:=515, Height:=225)
        .Chart.SetSourceData Source:=Sheet10.Range("T" & iStart & ":T" & iEnd)
        .Chart.ChartType = xlLineMarkers
        .Chart.ChartTitle.Characters.Text = "SC5 " & StartDate & "-" & EndDate
        .Chart.SeriesCollection(1).XValues = Sheet10.Range("B" & iStart & ":B" & iEnd)
        .Chart.Axes(xlCategory).CategoryType = xlCategoryScale
        .Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Inch2/min"
        End With
        
        With Sheet1.ChartObjects.Add(Left:=775, Width:=375, Top:=515, Height:=225)
        .Chart.SetSourceData Source:=Sheet11.Range("T" & iStart & ":T" & iEnd)
        .Chart.ChartType = xlLineMarkers
        .HasTitle = True
        .Chart.ChartTitle.Characters.Text = "SC4 " & StartDate & "-" & EndDate
        .Chart.SeriesCollection(1).XValues = Sheet11.Range("B" & iStart & ":B" & iEnd)
        .Chart.Axes(xlCategory).CategoryType = xlCategoryScale
        .Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Inch2/min"
        End With

The first 4 graphs are executed perfectly, but when I got to the fifth, I get an error on the line which adds the title:

"This object has no title"

Does anyone know why this happens on that particular graph (and all others after it). I used the same line in previous charts and it worked just fine.

Thanks!!

 

[Andrew Poulsom]

Have you tried adding:

.HasTitle = True

like you did in the last example?

 

[nbuk]

Yes I did, and I still get an error. I forgot to change the last example back to my original code, but yes both versions give an error

 

[Andrew Poulsom]

What's in iStart and iEnd?

 

[nbuk]

Its just integer values to keep track of which ranges to chart.

After the error, If i go into debugging, and then stop it, the chart itself it present with the right data, but not formatted correctly sice the code was cutoff from the error line.

 

[Andrew Poulsom]

Can you post some sample data? Why are you only plotting one column?

 

[nbuk]

As this project is for work I cannot post my sheet but I will provide more information.

I have several sheets which store data for different pieces of equipment. The data being recorded it the total downtime and productivity output of equipment for each shift.

The first four charts are bar charts. It is a sum of the down times over a certain period of time.

The line charts are for productivity. The record the total output of a machine over a certain time frame.

Hope this helps, as I am really stuck on this.

 

[Andrew Poulsom]

It doesn't have to be real data, just an example for the chart that's giving you an error. You didn't answer my question about plotting only one column.

 

[nbuk]

The one column graph is just productivity over a time frame, i.e. one column of data.

There is no problem with the chart plotting the data, because like I said in my previous post, the data is all there.

I moved the "title" line to the end of the chart code and everything is fine. If I remove that line, the code executes without errors and I get exactly what I need, just without a title.


Do you know of any other ways to maybe re-write that line of code, as I cannot think of any other errors.

On another note, I was trying to create some other graphs in other parts of the code and I get the same error at the same spot. Could this be related to some setting or anything other than the code itself?

 

[Andrew Poulsom]

This worked for me in Excel 2003. What version of Excel are you using?

Sub Test()
    Const iStart As Long = 1
    Const iEnd As Long = 10
    Dim StartDate As Date, EndDate As Date
    StartDate = DateValue("01/01/2010")
    EndDate = StartDate + 9
    With Sheet1.ChartObjects.Add(Left:=390, Width:=375, Top:=480, Height:=225)
        .Chart.SetSourceData Source:=Sheet8.Range("T" & iStart & ":T" & iEnd)
        .Chart.ChartType = xlLineMarkers
        .Chart.HasTitle = True
        .Chart.ChartTitle.Characters.Text = "SC1 " & StartDate & "-" & EndDate
    End With
End Sub