VBA Express : Excel - Charts - Delete out-of-bounds data labels

Excel

Charts - Delete out-of-bounds data labels

Ease of Use

Easy

Version tested with

2000 (Win); X (Mac)

Submitted by:

BlueCactus

Description:

Removes chart data labels for points outside of the x-axis scale range.

Discussion:

When you change the x-axis scale properties for a chart, Excel hides data markers, and (most of the) data points that fall outside of the new axis range. Yet Excel has an irritating 'feature' : data labels outside the new axis range just get squished up against the ends of the chart without being hidden or deleted. This results in the need for much furious mouse-clicking in an attempt to manually clean up the chart without accidentally deleting data series. If you've experienced this problem, then this KB is for you! The code below includes two subroutines: 1) ClipLabels(yourChart as Chart) Call this from your VBA code to clean up the data labels in yourChart. (Intermediate knowledge required.) 2) ClipLabelsUI() Allows you to act on the activated chart from the Tools -> Macro -> Macros... dialog. (Basic knowledge required.) The sample file includes an additional subroutine, MakeChart(), which just creates a demo chart for testing. Instructions are given in that workbook. This code should be used to clean up finished products, not works-in-progress. This is because the data labels are *deleted* and may not easily be restored (depending on the initial method of creation). TECHNICAL: This code deletes by setting Chart.SeriesCollection(i).Points(j).HasDataLabel = False. This removes the DataLabel object from that point. Alternatives which might allow for easier recreation of data labels (depending on their type) are a) Chart.SeriesCollection(i).Points(j).DataLabel.Text = "", or b) Chart.SeriesCollection(i).Points(j).DataLabel.Type = xlDataLabelsShowNone

Code:

instructions for use

			Option Explicit 
 
 ' This is just a stub to access ClipLabels() from the Macros menu.
 ' You don't need this Sub if you're only accessing through VBA.
 
Sub ClipLabelsUI() 
    On Error Resume Next 
    Call ClipLabels(ActiveChart) 
    On Error GoTo 0 
End Sub 
 
 ' This is the good stuff.
 
Sub ClipLabels(thisChart As Chart) 
    Dim i As Integer, j As Integer, xSeries As Variant 
     
    Application.ScreenUpdating = False 
     
    With thisChart.SeriesCollection 
        For i = 1 To .Count 
            xSeries = .Item(i).XValues 
            For j = 1 To .Item(i).Points.Count 
                If .Item(i).Points(j).HasDataLabel Then .Item(i).Points(j).HasDataLabel = _ 
                (xSeries(j) >= thisChart.Axes(xlCategory).MinimumScale) And _ 
                (xSeries(j) <= thisChart.Axes(xlCategory).MaximumScale) 
            Next j 
        Next i 
    End With 
     
    Application.ScreenUpdating = True 
End Sub

How to use:

Either: a) download the sample file, expand it, and open it in Excel; or b) do the following:
Copy the code above to the clipboard
Open Excel
Open the VBE by pressing Alt + F11
(Menus) Insert -> Module
Paste the code into the window that appears
(Menus) Close and Return to Microsoft Excel

Test the code:

Create a chart, add data labels, change the x-axis scale range to exclude some data labels from the chosen range.
Select the chart container.
(Menus) Tools -> Macro -> Macros... -> ClipLabelsUI() -> Run
*VBA Coders: Move the code to your own module and call with Call ClipLabels(yourChart as Chart). Note that Option Explicit can only appear at the start of the module. If you are inserting this code into your own module, you may have to remove Option Explicit from this code.

Sample File:

excel_chart_cliplabels.xls.zip 11.63KB

Approved by mdmackillop

This entry has been viewed 112 times.