Relative Chart Data Label Positioning

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
620
I have a clustered stacked column chart. The stacked columns are set to 0% gap width, so they actually appear side-by-side rather than on top of each other. This works wells for me, except when it comes to data labels. Because the series are defined as stacked, the option for "outside end" is not available. I would like for the data labels to appear just above each bar. I believe I need to incorporate a VBA routine that sets the label position above the value of the bar because the chart is also dynamic. That is, the data changes based on other selections I make in the workbook. So if I manually drag the labels above the value, they can be wildly out of proportion once the data changes.

Can you still use VBA to apply an outside end position for chart labels even if it's not available through the Excel GUI?

or - Can someone show me an example of a loop that will cycle through the chart series and each value, and set the label position a relative position above the series value, such as value + 5 pixels?

Please let me know if the background or request is unclear in any way.


Thanks,
 

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
620
Here's what I've come up with. At this point, perhaps the community can provide feedback to optimize and make the code more efficient, or propose alternatives:

Code:
Sub ModDataLabels()

Application.ScreenUpdating = False
Dim newTop As Long

Sheets("__SHEET NAME___").ChartObjects("___CHART NAME___").Activate

'Remove existing labels:
ActiveChart.SetElement (msoElementDataLabelNone)
        
With ActiveChart
    For x = 1 To .SeriesCollection.Count
            
        'Add labels at known positioning for desired series:
        .FullSeriesCollection(x).HasDataLabels = True
        
        'Format DataLabels:
        With .FullSeriesCollection(x)
            .DataLabels.Position = xlLabelPositionInsideEnd
            .DataLabels.NumberFormat = "#,###;;;"
            .HasLeaderLines = False
            .DataLabels.Font.Bold = True
        End With
  
        'Adjust label position:
        For y = 2 To .FullSeriesCollection(x).Points.Count

            On Error Resume Next 'DataLabels don't exist for each point; skip where appropriate
            
            newTop = .FullSeriesCollection(x).Points(y).DataLabel.Top - 25
            
            'Apply new values:
            .FullSeriesCollection(x).Points(y).DataLabel.Top = newTop
        Next y
    Next x
End With

Application.ScreenUpdating = True
End Sub
 
Last edited:

Forum statistics

Threads
1,082,246
Messages
5,363,984
Members
400,772
Latest member
solbebe

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