Fonts get size 1 and can't be changed after a procedure - even by excel GUI!!

Hi,

I have been using code to loop for each chtobj in a worksheet to set font
size right etc.

Sometimes an error occur and the size become 1 pt. I can live with that once
or so, but I can't change the FONT BACK neither with code nor from excel
chart GUI. No code are running in background during this of cource, I try
even to change the font from Excel GUI with macro switched off. It can only
be solved with building a new chartobject...

Have any of you got this error, any knowing why it occure and what I can do
about it?

I thougt it was because some of the charts had no values or some refere to
empty cells, but now, after some hours fixing this, the probelm still come
to me....

Please tell me!

For the interested:
The procedure I ran is an ordinary kind of:

LIKE:
Dim scol As Series
Dim dl As DataLabel
For Each scol In chtobj.Chart.SeriesCollection
For Each dl In scol.datalabels
With dl.Font
.Name = "Arial"
.FontStyle = "Fet"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
With dl
.NumberFormat = "#,##0"
.AutoScaleFont = True
End With
Next
Next

OR

'chtobj.Chart.Legend.AutoScaleFont = True
With chtobj.Chart.Legend.Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
chtobj.Chart.Legend.Position = xlBottom



Regards

I just found that if I deactivate the autosize function it work again. And
if I try to activate the autosize again, i get a error message "there is not
allowed with more fonts in the sheet" or something.

I know I have read something about autosize funktion combined with many
chartobjects. Any knowing if I can get rid of the probelms if I just set
font autosize function to false instead in the code?

Regards


"Marie J-son" <Not@email.com> skrev i meddelandet
news:uVNmiSy2EHA.3000@TK2MSFTNGP15.phx.gbl...
> Hi,
>
> I have been using code to loop for each chtobj in a worksheet to set font
> size right etc.
>
> Sometimes an error occur and the size become 1 pt. I can live with that
> once or so, but I can't change the FONT BACK neither with code nor from
> excel chart GUI. No code are running in background during this of cource,
> I try even to change the font from Excel GUI with macro switched off. It
> can only be solved with building a new chartobject...
>
> Have any of you got this error, any knowing why it occure and what I can
> do about it?
>
> I thougt it was because some of the charts had no values or some refere to
> empty cells, but now, after some hours fixing this, the probelm still come
> to me....
>
> Please tell me!
>
> For the interested:
> The procedure I ran is an ordinary kind of:
>
> LIKE:
> Dim scol As Series
> Dim dl As DataLabel
> For Each scol In chtobj.Chart.SeriesCollection
> For Each dl In scol.datalabels
> With dl.Font
> .Name = "Arial"
> .FontStyle = "Fet"
> .Size = 8
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> .ColorIndex = xlAutomatic
> .Background = xlAutomatic
> End With
> With dl
> .NumberFormat = "#,##0"
> .AutoScaleFont = True
> End With
> Next
> Next
>
> OR
>
> 'chtobj.Chart.Legend.AutoScaleFont = True
> With chtobj.Chart.Legend.Font
> .Name = "Arial"
> .FontStyle = "Normal"
> .Size = 8
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> .ColorIndex = xlAutomatic
> .Background = xlAutomatic
> End With
> chtobj.Chart.Legend.Position = xlBottom
>
>
>
> Regards
>

Marie -

I have a brief description of this problem on my web site:

http://peltiertech.com/Excel/Charts/FixFonts.html

In this page I treat it as an annoyance related to the appearance of the fonts in
the charts, but you're seeing it as a real problem with the chart's function.
Basically, setting AutoFontScale to False will double the number of charts you may
have without getting these problems.

The page above also describes a procedure to make the default setting for
AutoFontScale = False.

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

Marie J-son wrote:

> I just found that if I deactivate the autosize function it work again. And
> if I try to activate the autosize again, i get a error message "there is not
> allowed with more fonts in the sheet" or something.
>
> I know I have read something about autosize funktion combined with many
> chartobjects. Any knowing if I can get rid of the probelms if I just set
> font autosize function to false instead in the code?
>
> Regards
>
>
> "Marie J-son" <Not@email.com> skrev i meddelandet
> news:uVNmiSy2EHA.3000@TK2MSFTNGP15.phx.gbl...
>
>>Hi,
>>
>>I have been using code to loop for each chtobj in a worksheet to set font
>>size right etc.
>>
>>Sometimes an error occur and the size become 1 pt. I can live with that
>>once or so, but I can't change the FONT BACK neither with code nor from
>>excel chart GUI. No code are running in background during this of cource,
>>I try even to change the font from Excel GUI with macro switched off. It
>>can only be solved with building a new chartobject...
>>
>>Have any of you got this error, any knowing why it occure and what I can
>>do about it?
>>
>>I thougt it was because some of the charts had no values or some refere to
>>empty cells, but now, after some hours fixing this, the probelm still come
>>to me....
>>
>>Please tell me!
>>
>>For the interested:
>>The procedure I ran is an ordinary kind of:
>>
>>LIKE:
>> Dim scol As Series
>> Dim dl As DataLabel
>> For Each scol In chtobj.Chart.SeriesCollection
>> For Each dl In scol.datalabels
>> With dl.Font
>> .Name = "Arial"
>> .FontStyle = "Fet"
>> .Size = 8
>> .Strikethrough = False
>> .Superscript = False
>> .Subscript = False
>> .OutlineFont = False
>> .Shadow = False
>> .Underline = xlUnderlineStyleNone
>> .ColorIndex = xlAutomatic
>> .Background = xlAutomatic
>> End With
>> With dl
>> .NumberFormat = "#,##0"
>> .AutoScaleFont = True
>> End With
>> Next
>> Next
>>
>>OR
>>
>> 'chtobj.Chart.Legend.AutoScaleFont = True
>> With chtobj.Chart.Legend.Font
>> .Name = "Arial"
>> .FontStyle = "Normal"
>> .Size = 8
>> .Strikethrough = False
>> .Superscript = False
>> .Subscript = False
>> .OutlineFont = False
>> .Shadow = False
>> .Underline = xlUnderlineStyleNone
>> .ColorIndex = xlAutomatic
>> .Background = xlAutomatic
>> End With
>> chtobj.Chart.Legend.Position = xlBottom
>>
>>
>>
>>Regards
>>
>
>
>