Multiple fonts in a chart title

[rdw72777]

It looks like this might be impossible, but I was just curious if anyone had solved this.

Basically I have a 3 line chart title where the first line is Bold anf 18 point font, the second line is 14 point and itallic and bold and the third line is 12 point and italic.

I set the chart title through code below:

Sheets(myRegion).ChartTitle.Text = _
     "East Region" & Chr(13) & "PTS Actual/Projected FTE Utilisation by Project" & _
          Chr(13) & "(for the 18 months ended September 30, 2012)" & Chr(13) & ""

So I'd like "East Region" to be 18 point font, "PTS Actual/Projected FTE Utilisation by Project" to be 14 point and bold and "(for the 18 months ended September 30, 2012)" to be 12 point and itallic.

Has anyone solved this using the ChartTitle? I'm sure it could be done by inserting 3 text boxes but that isn't an option I'm allowed in this particular exercise....

 

[Andrew Poulsom]

Try:

    With Sheets(myRegion).ChartTitle
        .Text = _
            "East Region" & Chr(13) & "PTS Actual/Projected FTE Utilisation by Project" & _
            Chr(13) & "(for the 18 months ended September 30, 2012)" & Chr(13) & ""
        With .Characters(Start:=1, Length:=Len("East Region")).Font
            .FontStyle = "Bold"
            .Size = 18
        End With
        With .Characters(Start:=Len("East Region") + 2, Length:=Len("PTS Actual/Projected FTE Utilisation by Project")).Font
            .FontStyle = "Bold"
            .Size = 14
        End With
        With .Characters(Start:=Len("East Region" & Chr(13) & "PTS Actual/Projected FTE Utilisation by Project") + 2, Length:=Len("(for the 18 months ended September 30, 2012)")).Font
            .FontStyle = "Italic"
            .Size = 12
        End With
    End With

 

[rdw72777]

Thanks, Andrew. that didn't work but its pretty much what I expected. The more I have been reading, the more it looks like its a limitation of Excel 2007 and not VBA itself.

(Note: http://www.mrexcel.com/forum/showthread.php?t=422007&highlight=chart+title+length)

Seems I'll either have to do it manually or use the textbox method. Sad that this is so complicated but oh well....

 

[Andrew Poulsom]

Yeah, I tested the code in Excel 2003.

 

[rdw72777]

Curious about maybe a different workaround since I only have Excel 2007.

I use a macro tos et the chart tile to begin with, using a template for the chart. the chart template has the correct formatting beofre I set the chart title in my code. For instance, before it says "East Region" it simply says "My Region".

Is there a way to do find->replace with a chart title? If so, maybe doing so wouldn't tweak the chart title formatting the same way setting the charttitle.text method does.

 

[Andrew Poulsom]

Is there a way to do find->replace with a chart title?

I don't think so.