Forum Discussion

AramA92's avatar
AramA92
Copper Contributor
Aug 22, 2023

Overlapping labels in stacked bar charts

Hey,

 

I am facing the issue of overlapping data labels visible for illustrations. I want to avoid doing a manual solution since I wont be the only user of this spreadsheet. 

 

I have been trying different VBA codes to get around the problem, but often run into the "438 overflow" error.

 

Does anyone have any advice on how to write a VBA code which automatically separates overlapping data labels when using the "Refresh data" function?

 

All help is appreciated!

 

Br

Aram

4 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    AramA92 

    To automatically avoid overlapping data labels in stacked bar charts in Excel using VBA, you can create a macro that adjusts the label positions.

    Here is an example of a VBA code that does this:

    Sub AdjustDataLabels()
    'This code is only an example, you may have to adapt it to your needs.
        Dim cht As Chart
        Dim ser As Series
        Dim i As Long
        Dim rng As Range
        
        ' Set the chart object
        Set cht = ActiveSheet.ChartObjects(1).Chart
        
        ' Loop through all series in the chart
        For Each ser In cht.SeriesCollection
            ' Check if the series has data labels
            If ser.HasDataLabels Then
                ' Loop through each point in the series
                For i = 1 To ser.Points.Count
                    ' Set the range for the data label
                    Set rng = ser.Points(i).DataLabel
                    ' Check if the data label is visible
                    If rng.Visible Then
                        ' Check for overlap with other data labels
                        Do While CheckOverlap(ser, i, rng)
                            ' Adjust the position of the data label
                            rng.Left = rng.Left + 5 ' You can adjust this value as needed
                        Loop
                    End If
                Next i
            End If
        Next ser
    End Sub
    
    Function CheckOverlap(ser As Series, index As Long, rng As Range) As Boolean
        Dim i As Long
        Dim otherRng As Range
        
        ' Loop through other points in the series
        For i = 1 To ser.Points.Count
            If i <> index Then
                ' Get the data label of the other point
                Set otherRng = ser.Points(i).DataLabel
                ' Check if the data labels overlap
                If Not (rng.Left + rng.Width < otherRng.Left Or _
                        rng.Left > otherRng.Left + otherRng.Width Or _
                        rng.Top + rng.Height < otherRng.Top Or _
                        rng.Top > otherRng.Top + otherRng.Height) Then
                    ' Overlap detected
                    CheckOverlap = True
                    Exit Function
                End If
            End If
        Next i
        ' No overlap detected
        CheckOverlap = False
    End Function

    This VBA code iterates through each series in the chart, then through each data point in each series. For each data point, it checks if the data label is visible and if it overlaps with other data labels. If an overlap is detected, it adjusts the position of the data label to the right (you can adjust the value rng.Left + 5 to control the adjustment).

    To use this code:

    1. Open your Excel workbook with the stacked bar chart.
    2. Press ALT + F11 to open the VBA editor.
    3. Insert a new module (right-click on "Modules" in the Project Explorer and choose "Insert" > "Module").
    4. Paste the code into the module.
    5. Close the VBA editor.
    6. Run the macro by pressing ALT + F8, selecting "AdjustDataLabels," and clicking "Run."

    This code should help automatically adjust data labels to avoid overlap when refreshing data in your stacked bar chart.The text, steps and the code were created with the help of AI.

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    • dgarden's avatar
      dgarden
      Copper Contributor

      NikolinoDE Thanks for sharing! I'm getting a "Run-time error '13' type mismatch error" when running this. It doesn't like this line: Set rng = ser.Points(i).DataLabel

       

      Any ideas?

      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor

        dgarden 

        The "Run-time error '13' Type mismatch" error typically occurs when there is a mismatch between the data type expected by the code and the actual data type of the object being used. In this case, it is likely that the DataLabel property is not returning a valid Range object.

        The DataLabel property for a Point in a chart series does not return a Range object; instead, it returns a DataLabel object. To fix the "Type mismatch" error, you should declare rng as a DataLabel object rather than a Range object.

        Here is the modified code:

        Sub AdjustDataLabels()
            Dim cht As Chart
            Dim ser As Series
            Dim i As Long
            Dim rng As DataLabel ' Declare rng as a DataLabel object
            
            ' Set the chart object
            Set cht = ActiveSheet.ChartObjects(1).Chart
            
            ' Loop through all series in the chart
            For Each ser In cht.SeriesCollection
                ' Check if the series has data labels
                If ser.HasDataLabels Then
                    ' Loop through each point in the series
                    For i = 1 To ser.Points.Count
                        ' Set the range for the data label
                        Set rng = ser.Points(i).DataLabel
                        ' Check if the data label is visible
                        If rng.Visible Then
                            ' Check for overlap with other data labels
                            Do While CheckOverlap(ser, i, rng)
                                ' Adjust the position of the data label
                                rng.Left = rng.Left + 5 ' You can adjust this value as needed
                            Loop
                        End If
                    Next i
                End If
            Next ser
        End Sub

        By declaring rng as a DataLabel object, you should be able to avoid the "Type mismatch" error when running the code.

        If you have error again, please give with the error more information’s about your Excel version, operating system, storage medium. The proposed steps/solutions/Codes are untested. The text and steps were edited with the help of AI.

         

         

        My answers are voluntary and without guarantee!

         

        Hope this will help you.

        Was the answer useful? Mark as best response and Like it!

        This will help all forum participants.

Resources