Has anyone had problems setting the font size for a title through VBA?

I have some VBA code that makes multiple copies of a chart object and then populates the chart with series and title information. Unfortunately, the font size for the chart title keeps on getting reset to 16.5, even though I set it to 12 via VBA. I have no idea why this is, and no error is listed. Any ideas on how to fix this would be really welcome.

Further details:

Code used to duplicate the original chart object

Set co2 = co.Duplicate

Code used to rename and change font

co2.Chart.ChartTitle.Text = cYNav.getName
co2.Chart.ChartTitle.Font.Size = 12

where co2 is a ChartObject
cYNav is an object I created to store some info.

Thank you for your help.

Regards,

Viv.

Does it help to include this line?

co2.Chart.ChartTitle.AutoScaleFont = False

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

Viv2004 wrote:

> I have some VBA code that makes multiple copies of a chart object and
> then populates the chart with series and title information.
> Unfortunately, the font size for the chart title keeps on getting reset
> to 16.5, even though I set it to 12 via VBA. I have no idea why this is,
> and no error is listed. Any ideas on how to fix this would be really
> welcome.
>
> Further details:
>
> Code used to duplicate the original chart object
>
> Set co2 = co.Duplicate
>
> Code used to rename and change font
>
> co2.Chart.ChartTitle.Text = cYNav.getName
> co2.Chart.ChartTitle.Font.Size = 12
>
> where co2 is a ChartObject
> cYNav is an object I created to store some info.
>
> Thank you for your help.
>
> Regards,
>
> Viv.
>
>

Viv2004 -

> I have some VBA code that makes multiple copies of a chart object and then
> populates the chart with series and title information. Unfortunately, the
> font size for the chart title keeps on getting reset to 16.5, even though
> I set it to 12 via VBA. I have no idea why this is, and no error is
> listed. Any ideas on how to fix this would be really welcome. <

If it's the active chart, try

ActiveChart.ChartTitle.AutoScaleFont = False

Or, adapt to your situation.

- Mike
www.mikemiddleton.com

Mike & Jon, that worked perfectly and solved the problem. Thank you very much for your help!

Regards,

Viv.

On Mon, 22 Aug 2005 16:57:49 -0500, Viv2004
<[email protected]> wrote:

Hopefully, this is on point. I've noticed with the following code,
that if I change the font size of a portion of the chart title, I get
two curious conditions. First off, the plot area grows in size and
climbs up the page by a few units for each invocation of the macro.
Say for the first time thru, the height = 300. On successive passes
thru the macro, it will be 304, then 307, 311, etc., until it fills up
the entire chart area. Also, the fonts on all axes and titles begins
to autoscale down to ever smaller sizes to the point they become
microscopic.

All fonts are set to autoscale from a maximum window size of 10pt with
exception to the first part of the .ChartTitle.Text which is set on
the graph directly to a 20pt size.

I solved the first issue by forcing the plot area to fixed height and
plot values. The disappearing font size remains still, even with the
constant plot area height. Could the issue be the difference in font
sizes within the title.

Would like to keep as much autoscaling on as possible as the people
that use this chart have very busy desktops and usually resize the
Excel windows a lot.

remove the XXX in the email address to chat with me

---dave


With wsTrend
.HasTitle = True
With .ChartTitle
.Text = "S0 - DC2007" & vbCr & strAppl & vbCr & strPart & _
" " & strDept & " Date: " & Format(Now, "mm/dd/yyyy")
x = InStr(.Text, "P.N.")
y = Len(.Text)
.Characters(start:=x, Length:=y).Font.Size = 14
End With
With .Axes(xlCategory, xlPrimary)
.HasTitle = True
.AxisTitle.Characters.Text = strTitle
.TickLabelSpacing = wsData.[X_Scale]
End With
With .Axes(xlValue, xlPrimary)
.HasTitle = True
.AxisTitle.Characters.Text = wsData.[Unit_1st]
.MinimumScale = wsData.[Y_Scale]
End With
With .PlotArea
Msgbox .Height & " " & .Top
' .Height = 300
'.Top = 100
End With
End With


>
>I have some VBA code that makes multiple copies of a chart object and
>then populates the chart with series and title information.
>Unfortunately, the font size for the chart title keeps on getting reset
>to 16.5, even though I set it to 12 via VBA. I have no idea why this is,
>and no error is listed. Any ideas on how to fix this would be really
>welcome.
>
>Further details:
>
>Code used to duplicate the original chart object
>
>Set co2 = co.Duplicate
>
>Code used to rename and change font
>
>co2.Chart.ChartTitle.Text = cYNav.getName
>co2.Chart.ChartTitle.Font.Size = 12
>
>where co2 is a ChartObject
>cYNav is an object I created to store some info.
>
>Thank you for your help.
>
>Regards,
>
>Viv.