Re: Change datalabel font size for all chartobjects in a row

I get error like "Size method not allowed by Font object" for this code. Can
sombodey help me?

/Regards

-----
"John Green" <greenj@spam.net.au> skrev i meddelandet
news:e60uYYezEHA.3908@TK2MSFTNGP12.phx.gbl...
> Try the following:

Sub SetFonts()
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim chtobj As ChartObject
Dim scol As Series
Dim dl As DataLabel
For Each chtobj In ActiveSheet.ChartObjects
For Each scol In chtobj.Chart.SeriesCollection
For Each dl In scol.datalabels
With dl.Font
.Name = "Arial"
.FontStyle = "Fet"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
.Background = xlTransparent
End With
With dl
.NumberFormat = "#,##0"
.AutoScaleFont = True
End With
Next
Next
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub



> John Green
> Sydney
> Australia
>
>
> "Marie J-son" <Not@email.com> wrote in message
> news:u6hVHIdzEHA.1204@TK2MSFTNGP10.phx.gbl...
>> Hi,
>> This Sub doesn't work (why?)
>>
>> I have a number of chartobjects with different numbers of datalabels and
>> seriecollections. How can I change font size for all charts and
>> datalabels
>> in seriescollections all in a row?
>>
>> Sub SetFonts()
>> Dim chtobj As ChartObjects
>> Dim scol As SeriesCollection
>> Dim dl As DataLabel
>> For Each chtobj In ActiveSheet
>> For Each scol In chtobj
>> For Each dl In scol
>> With dl.Font
>> .Name = "Arial"
>> .FontStyle = "Fet"
>> .Size = 16
>> .Strikethrough = False
>> .Superscript = False
>> .Subscript = False
>> .OutlineFont = False
>> .Shadow = False
>> .Underline = xlUnderlineStyleNone
>> .ColorIndex = xlAutomatic
>> .Background = xlAutomatic
>> .NumberFormat = "#,##0"
>> .AutoScaleFont = True
>> End With
>> Next
>> Next
>> Next
>> End Sub
>>
>>
>> Kind regards
>>
>>
>
>

Furtther information:
I put a msgbox the line before "With dl.Font" to tell the name of the
activeChart and than I see that the loop never leave the first
activechart...

/regards


"Marie J-son" <Not@email.com> skrev i meddelandet
news:e$az2to2EHA.2592@TK2MSFTNGP09.phx.gbl...
>I get error like "Size method not allowed by Font object" for this code.
>Can sombodey help me?
>
> /Regards
>
> -----
> "John Green" <greenj@spam.net.au> skrev i meddelandet
> news:e60uYYezEHA.3908@TK2MSFTNGP12.phx.gbl...
>> Try the following:
>
> Sub SetFonts()
> Application.EnableEvents = False
> Application.ScreenUpdating = False
> Dim chtobj As ChartObject
> Dim scol As Series
> Dim dl As DataLabel
> For Each chtobj In ActiveSheet.ChartObjects
> For Each scol In chtobj.Chart.SeriesCollection
> For Each dl In scol.datalabels
> With dl.Font
> .Name = "Arial"
> .FontStyle = "Fet"
> .Size = 10
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> .ColorIndex = 1
> .Background = xlTransparent
> End With
> With dl
> .NumberFormat = "#,##0"
> .AutoScaleFont = True
> End With
> Next
> Next
> Next
> Application.EnableEvents = True
> Application.ScreenUpdating = True
> End Sub
>
>
>
>> John Green
>> Sydney
>> Australia
>>
>>
>> "Marie J-son" <Not@email.com> wrote in message
>> news:u6hVHIdzEHA.1204@TK2MSFTNGP10.phx.gbl...
>>> Hi,
>>> This Sub doesn't work (why?)
>>>
>>> I have a number of chartobjects with different numbers of datalabels and
>>> seriecollections. How can I change font size for all charts and
>>> datalabels
>>> in seriescollections all in a row?
>>>
>>> Sub SetFonts()
>>> Dim chtobj As ChartObjects
>>> Dim scol As SeriesCollection
>>> Dim dl As DataLabel
>>> For Each chtobj In ActiveSheet
>>> For Each scol In chtobj
>>> For Each dl In scol
>>> With dl.Font
>>> .Name = "Arial"
>>> .FontStyle = "Fet"
>>> .Size = 16
>>> .Strikethrough = False
>>> .Superscript = False
>>> .Subscript = False
>>> .OutlineFont = False
>>> .Shadow = False
>>> .Underline = xlUnderlineStyleNone
>>> .ColorIndex = xlAutomatic
>>> .Background = xlAutomatic
>>> .NumberFormat = "#,##0"
>>> .AutoScaleFont = True
>>> End With
>>> Next
>>> Next
>>> Next
>>> End Sub
>>>
>>>
>>> Kind regards
>>>
>>>
>>
>>
>
>

Marie -

Your procedure works fine for me, changing all the labels to the desired font size.

You also don't need to loop through each data label if the set of them will be the same:

Sub SetFonts()
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim chtobj As ChartObject
Dim scol As Series
Dim dl As DataLabel
For Each chtobj In ActiveSheet.ChartObjects
For Each scol In chtobj.Chart.SeriesCollection
With scol.DataLabels.Font
.Name = "Arial"
.FontStyle = "Fet"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
.Background = xlTransparent
End With
With scol.DataLabels
.NumberFormat = "#,##0"
.AutoScaleFont = True
End With
Next
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

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


Marie J-son wrote:

> I get error like "Size method not allowed by Font object" for this code. Can
> sombodey help me?
>
> /Regards
>
> -----
> "John Green" <greenj@spam.net.au> skrev i meddelandet
> news:e60uYYezEHA.3908@TK2MSFTNGP12.phx.gbl...
>
>>Try the following:
>
>
> Sub SetFonts()
> Application.EnableEvents = False
> Application.ScreenUpdating = False
> Dim chtobj As ChartObject
> Dim scol As Series
> Dim dl As DataLabel
> For Each chtobj In ActiveSheet.ChartObjects
> For Each scol In chtobj.Chart.SeriesCollection
> For Each dl In scol.datalabels
> With dl.Font
> .Name = "Arial"
> .FontStyle = "Fet"
> .Size = 10
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> .ColorIndex = 1
> .Background = xlTransparent
> End With
> With dl
> .NumberFormat = "#,##0"
> .AutoScaleFont = True
> End With
> Next
> Next
> Next
> Application.EnableEvents = True
> Application.ScreenUpdating = True
> End Sub
>
>
>
>
>>John Green
>>Sydney
>>Australia
>>
>>
>>"Marie J-son" <Not@email.com> wrote in message
>>news:u6hVHIdzEHA.1204@TK2MSFTNGP10.phx.gbl...
>>
>>>Hi,
>>>This Sub doesn't work (why?)
>>>
>>>I have a number of chartobjects with different numbers of datalabels and
>>>seriecollections. How can I change font size for all charts and
>>>datalabels
>>>in seriescollections all in a row?
>>>
>>>Sub SetFonts()
>>>Dim chtobj As ChartObjects
>>>Dim scol As SeriesCollection
>>>Dim dl As DataLabel
>>>For Each chtobj In ActiveSheet
>>> For Each scol In chtobj
>>> For Each dl In scol
>>> With dl.Font
>>> .Name = "Arial"
>>> .FontStyle = "Fet"
>>> .Size = 16
>>> .Strikethrough = False
>>> .Superscript = False
>>> .Subscript = False
>>> .OutlineFont = False
>>> .Shadow = False
>>> .Underline = xlUnderlineStyleNone
>>> .ColorIndex = xlAutomatic
>>> .Background = xlAutomatic
>>> .NumberFormat = "#,##0"
>>> .AutoScaleFont = True
>>> End With
>>> Next
>>> Next
>>>Next
>>>End Sub
>>>
>>>
>>>Kind regards
>>>
>>>
>>
>>
>
>