Problem with Chart Title Formatting

I have what I thought would be a very simple task. I want the first line of
a Chart title to be a large font size, while making the second line a smaller
font size.

I wrote the VBA code listed below. I thought all I would need to do is use
the Characters property to change each part of the title. I did so and
everything seemed to work OK until I decided to try and format the 2nd line
using italics.

By inserting the italics line (which is currently commented out), excel
chages the font size to the same size as the 1st line (20 in this case)

Does anybody see an obvious mistake? Could someon try pasting this code
into their VBE and try it out on a chart with/with out the italic line? For
purposes of this example, assume Line 1 reads "TEST", while line 2 reads "Why
does this not work". Your help would be much appreciated.

Thanks




Sub FormatChart1Title()
Set Cht = Charts(1)
Cht.Select
With Cht.ChartTitle
.AutoScaleFont = False
With .Characters(Start:=1, Length:=5).Font
.Name = "Franklin Gothic Book"
.Bold = True
.Size = 20
.Italic = False
End With

.AutoScaleFont = False

With .Characters(Start:=6, Length:=22).Font
.Name = "Franklin Gothic Book"
.Bold = True
.Size = 8
'.Italic = True
End With
End With
Cht.Select

End Sub

Kevin -

Sometimes text objects cannot be reliably formatted character by character, if
you're doing too many different formats. But yours is pretty straightforward.

It's better to format the entire text object, then just format the part that's
different. I've amended your procedure below, and it worked just as I expected
(yours worked just as you'd described).

Sub FormatChart1Title()
Dim Cht As Chart

Set Cht = Charts(1)

With Cht.ChartTitle
.AutoScaleFont = False

' Format the entire title
With .Font
.Name = "Franklin Gothic Book"
.Bold = True
.Size = 20
End With

' Reformat just one part
With .Characters(Start:=6, Length:=22).Font
.Italic = True
.Size = 8
End With

End With

End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


KvnLynch wrote:

> I have what I thought would be a very simple task. I want the first line of
> a Chart title to be a large font size, while making the second line a smaller
> font size.
>
> I wrote the VBA code listed below. I thought all I would need to do is use
> the Characters property to change each part of the title. I did so and
> everything seemed to work OK until I decided to try and format the 2nd line
> using italics.
>
> By inserting the italics line (which is currently commented out), excel
> chages the font size to the same size as the 1st line (20 in this case)
>
> Does anybody see an obvious mistake? Could someon try pasting this code
> into their VBE and try it out on a chart with/with out the italic line? For
> purposes of this example, assume Line 1 reads "TEST", while line 2 reads "Why
> does this not work". Your help would be much appreciated.
>
> Thanks
>
>
>
>
> Sub FormatChart1Title()
> Set Cht = Charts(1)
> Cht.Select
> With Cht.ChartTitle
> .AutoScaleFont = False
> With .Characters(Start:=1, Length:=5).Font
> .Name = "Franklin Gothic Book"
> .Bold = True
> .Size = 20
> .Italic = False
> End With
>
> .AutoScaleFont = False
>
> With .Characters(Start:=6, Length:=22).Font
> .Name = "Franklin Gothic Book"
> .Bold = True
> .Size = 8
> '.Italic = True
> End With
> End With
> Cht.Select
>
> End Sub