This object has no title

[o4008]

For some weird reason, when I run the macro I created at work, it doesn't behave like it should. My macro creates charts. When it goes through the code, all of a sudden it tells me that my chart doesn't have a title - but when I step into it (pressing F8), it works fine. This is the runtime error I get: '-2147024809 (80070057) This object has no title.

This is the code I have:

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Annual_Sales'!$A$6:$D$10")
    ActiveWorkbook.ShowPivotChartActiveFields = True
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.ChartStyle = 41
    ActiveChart.ClearToMatchStyle
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).ApplyDataLabels
    ActiveChart.SeriesCollection(1).DataLabels.Select
    Selection.Position = xlLabelPositionAbove
    ActiveChart.SeriesCollection(1).DataLabels.NumberFormat = "$#,##"
    ActiveChart.SeriesCollection(1).DataLabels.Font.Size = 12
    ActiveChart.Legend.Select
    Selection.Delete
    ActiveChart.Axes(xlValue).Select
    Selection.Delete
    ActiveChart.ChartTitle.Characters.Text = "Yearly Sales"

[Andy Pope]

It's probably a timing issue.
First try adding a line before setting the title text

    ActiveChart.HasTitle = True
   ActiveChart.ChartTitle.Characters.Text = "Yearly Sales"

[TheURL]

I discovered this when migrating a working VBA app from 2k3 to 2k7 - its not a timing issue - tried that. In my case the solution was as follows:

ActiveChart.HasTitle = True
ActiveChart.HasTitle = False
ActiveChart.HasTitle = True
ActiveChart...etc

I thought it was odd - MsgBox ActiveChart.HasTitle produced True - but the Chart Tools ribbon sympathised with the RTE that had suggested the first line has silently failed!

Hope it works for you too.

Simon

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[TheURL]

I discovered this when migrating a working VBA app from 2k3 to 2k7 - its not a timing issue - tried that. In my case the solution was as follows:

ActiveChart.HasTitle = True
ActiveChart.HasTitle = False
ActiveChart.HasTitle = True
ActiveChart...etc

I thought it was odd - MsgBox ActiveChart.HasTitle produced True - but the Chart Tools ribbon sympathised with the RTE that had suggested the first line has silently failed!

Hope it works for you too.

Simon (reposted with tags as requested)