How to stop chart title appearing.

[Kelvin Stott]

I've written some nice VBA code to create a chart with no title, but when I allow the user to remove all but the last data series (with a userform), the name of that data series pops up automatically as the chart title. I tried to stop this with [chart_reference].HasTitle = False, but the name of the last data series still pops up as the chart title when only one data series remains...

Is there any other way/command to override this automatic title popping up?

 

[Andrew Poulsom]

What version of Excel are you using? Can we see your code?

 

[Kelvin Stott]

What version of Excel are you using? Can we see your code?

Thanks, Andrew,

I'm using Excel 2007, updated online to 2010 (not sure if there's a difference with virgin Excel 2010?).

I'd be happy to share the code, but it's very long and scattered across two modules (including a user form), so I'd feel bad for expecting anyone read through all that. But let me describe the basic logic, then you can tell me which bit(s) of code you want to see:

1. Userform contains check boxes to define what data series are included in chart. Any changes to these call a sub to recreate the chart
2. Create chart sub essentially recreates the chart from scratch, depending on what series are checked in the user form. This sub routine includes the [chartref].HasTitle = False command at the start, before each of selected data series are added one after the other.
3. The same create chart sub then saves the chart image as a bmp file and reloads this back into the open user form.

Clearly there is something going wrong in step 2, as the title pops up when only one data series is checked in the userform, despite the hastitle = False command. But I'm not sure why it's not working...

 

[Andrew Poulsom]

Can you write a short macro that exhibits the behaviour you describe?

Edit:

This may help you:

http://www.pcreview.co.uk/forums/apparent-excel-2007-chart-hastitle-race-condition-t4002702.html

 

[Kelvin Stott]

I guess the problem must be in here somewhere:

    Set DChart = ActiveSheet.Shapes.AddChart.Chart
    
    With DChart
        With .ChartArea
            .ClearContents
            .Height = DControl.ChartImage.Height
            .Width = DControl.ChartImage.Width
            .Font.Size = 10
            .Format.Fill.ForeColor.RGB = RGB(212, 208, 200)
            .Format.Line.Visible = msoFalse
        End With
        With .PlotArea
            .Format.Fill.ForeColor.RGB = RGB(255, 255, 221)
            .Format.Line.ForeColor.RGB = RGB(153, 153, 153)
            .Format.Line.Weight = 0.75
        End With
        .ChartType = xlXYScatterSmoothNoMarkers
[B]          .HasTitle = False[/B]
        .HasLegend = True
    End With

    If DControl.ShowCDF Then
    With DChart.SeriesCollection.NewSeries
        .Name = "CDF"
        .XValues = Prob
        .Values = QF
    End With
    End If
    
    If DControl.ShowPDF Then
    With DChart.SeriesCollection.NewSeries
        .Name = "PDF"
        .XValues = NPDF
        .Values = QF
    End With
    End If

    'Further code to add more data series goes here

 

[Andrew Poulsom]

I edited my post to include a link that may help you.

 

[Kelvin Stott]

I edited my post to include a link that may help you.

Thanks, Andrew.

So is this just a bug with Excel 2007/2010beta? I thought it was just me not getting to grips with VBA as I thought I had been (albeit slowly ).

 

[Kelvin Stott]

PS. Setting .HasTitle to True just before resetting it to False seemed to do the job. Clearly this is a bug with Excel.

Anyway, thanks again for your help Andrew.

 

[Kelvin Stott]

PS. Setting .HasTitle to True just before resetting it to False seemed to do the job. Clearly this is a bug with Excel.

Anyway, thanks again for your help Andrew.