VBA Excel Chart: Copy Series Datalabel formats

[mecg96]

Hello!

I'm trying to use VBA to copy the formatting of the datalabels of a specific data series in a chart, store that format in a variable(s) and then apply it to a different set of data labels.

I'm trying to get the info on font color, size, bold, underline, etc.

Any ideas on how to read and store all these properties ?

Thanks!

 

[pbornemeier]

What type of chart are you dealing with? Some types of charts' data labels have properties that others do not. The version of Excel you are using also makes a difference with 2003 and earlier being somewhat different from 2007 and 2010. There are about 30 properties for a DataLabel and several of them (like .Fill) have many options (Gradient, pattern, texture, Forecolor, Backcolor, etc.)
You can see them by using the VB editor bring up help on the DataLabels. One of the links on this page is to the DataLabel object and you can examine its properties.
To fully capture all of the properties of a Data Label, you will have to determine which properties you want to capture, then query the value in the series of interest.
If you don't really need to capture all of them the simplest way to do this would be to turn on the macro recorder and modify the default label to the style you want to copy. You will be able to determine what properties are important to you, rather than deal with all of them.

Here is a sample of querying a subset of the series properties:

Option Explicit

Sub CollectSeriesData()

    Dim sSeriesName As String
    Dim sTypeSelected As String
    Dim iBorderColorIndex As Integer
    Dim iBorderWeight As XlBorderWeight
    Dim iBorderLineStyle As XlLineStyle
    
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.SeriesCollection(1).Select
    
    sTypeSelected = TypeName(Selection)
    If sTypeSelected = "Series" Then
        sSeriesName = Selection.Name
        With Selection.DataLabels.Border
            iBorderColorIndex = .ColorIndex
            iBorderWeight = .Weight
            iBorderLineStyle = .LineStyle
        End With
        Stop
    Else
        MsgBox "You have not selected a Series.  You have selected a " & sTypeSelected
    End If
End Sub

Excel 2003 Macro Recorder Sample

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(3).DataLabels.Select
    With Selection.Border
        .ColorIndex = 51
        .Weight = xlMedium
        .LineStyle = xlDash
    End With
    Selection.Shadow = True
    With Selection.Interior
        .ColorIndex = 10
        .PatternColorIndex = 2
        .Pattern = xlSolid
    End With
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Aparajita"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    Selection.NumberFormat = "m/d/yyyy"
    With Selection
        .HorizontalAlignment = xlJustify
        .VerticalAlignment = xlTop
        .ReadingOrder = xlLTR
        .Position = xlLabelPositionCenter
        .Orientation = 34
    End With
End Sub

Excel 2010 Macro Recorder Sample

    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(1).DataLabels.Select
    Selection.ShowSeriesName = True
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0.3399999738
        .ForeColor.Brightness = 0
        .BackColor.ObjectThemeColor = msoThemeColorAccent1
        .BackColor.TintAndShade = 0.7649999857
        .BackColor.Brightness = 0
        .TwoColorGradient msoGradientHorizontal, 1
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorBackground2
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.5
        .Transparency = 0
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorBackground2
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.5
        .Transparency = 0.7299999893
    End With
    Selection.Format.Line.Style = msoLineThickThin
    With Selection.Format.Line
        .Visible = msoTrue
        .Weight = 1.75
    End With
    With Selection.Format.Shadow
        .Type = msoShadow25
        .Visible = msoTrue
        .Style = msoShadowStyleOuterShadow
        .Blur = 4
        .OffsetX = 2.4492935983E-16
        .OffsetY = 4
        .RotateWithShape = msoFalse
        .ForeColor.RGB = RGB(0, 0, 0)
        .Transparency = 0.5686300099
        .Size = 82
    End With

 

[tusharm]

VBA does not support a mechanism of getting every attribute of an object (and nested objects) w/o knowing the names of the attributes. So, either restrict yourself to a set of predetermined attributes or consider a .Net solution where, I believe, you can get all the attributes by using something called Reflection.

Hello!

I'm trying to use VBA to copy the formatting of the datalabels of a specific data series in a chart, store that format in a variable(s) and then apply it to a different set of data labels.

I'm trying to get the info on font color, size, bold, underline, etc.

Any ideas on how to read and store all these properties ?

Thanks!