VBA to Modify Chart Data Label Formats

[Raesu]

Hello,


I'd appreciate some advice on my attempt to format data labels the same way each time my VBA sub changes a charts data range (using VBA because the # of series changes). Conceptually I put the code together below but I can't figure out the correct structure for access the datalabel formats.

[pre]

Sub ChartTest()

With Worksheets("Regional Detail").ChartObjects("Chart 13").Chart

For Each SeriesCollection In Chart
DataLabels.NumberFormat = "#,##0.0"
DataLabels.Format.TextFrame2.TextRange.Font.Size = 12
Next SeriesCollection

End With

End Sub

[/pre]

Any suggestions? Thanks. (I'm getting type mismatch on the For Each line)

 

[Raesu]

Figured this out myself. In case this is helpful to anyone else, this code changes the data source and applies formatting to all data labels. It is linked to a dropdown_click event.


RegionRange is a range I set up prior to this code with some Select Case logic.

[pre]

With Worksheets("Regional Detail").ChartObjects("Chart 13").Chart

.SetSourceData _
Source:=RegionRange, _
PlotBy:=xlRows

x = .SeriesCollection.Count

For i = 1 To x
.SeriesCollection(i).DataLabels.NumberFormat = "[>=5]#,##0.0;;;"
.SeriesCollection(i).DataLabels.Format.TextFrame2.TextRange.Font.Size = 12
Next i

End With

[/pre]