set datalabel font size for all charts in a row

[Marie J-son]

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

 

[Jon Peltier]

You need to be more rigorous in how you spelunk the object model:

worksheet
chartobjects
chart object
chart
seriescollection
series
datalabels
datalabel

Partial code:

Dim chtob as ChartObject
Dim srs as Series
For Each chtob in ActiveSheet.ChartObjects
For Each srs in chtob.Chart.SeriesCollection
With srs.DataLabels.Font
.Name = "Arial"
' etc.

In the VB Editor, press the F2 function key. This opens the Object Browser, one of
your best friends in the VB Editor. This tells you which object is a parent or child
of another, so you can follow a progression such as the above.

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