VBA Excel Chart: Copy Series Datalabel formats

mecg96

New Member
Joined
May 24, 2012
Messages
3
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

Well-known Member
Joined
May 24, 2005
Messages
3,720
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:
Code:
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
Code:
    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
Code:
    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

MrExcel MVP
Joined
May 28, 2002
Messages
11,007
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!
 

Forum statistics

Threads
1,082,116
Messages
5,363,251
Members
400,722
Latest member
DrewPop24

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top