Setting Custom Data Labels for DonutChart

[TheTiredEngineer]

Im trying to format my data labels in a neat way to keep the chart from being so cluttered. I want to have some of the labels to the left of the actual chart and some on the right. Also trying to format so that it removes the zero value points and set the ones that are there to a custom number format of

"0;;;"

The code works to remove the zero value points but reformatting the good ones is breaking the code. Heres my code:

 pieChart.ApplyDataLabels _
    ShowValue:=True, _
    AutoText:=True, _
    LegendKey:=False, _
    HasLeaderLines:=False, _
    ShowSeriesName:=False, _
    ShowCategoryName:=True, _
    ShowPercentage:=False, _
    ShowBubbleSize:=False, _
    NumberFormat:="0;;"
     
'Loop through each data label and text for threshold
  For Each srs In pieChart.SeriesCollection
    For x = 1 To UBound(srs.Values)
      If Abs(srs.Values(x)) = Threshold Then
        srs.Points(x).DataLabel.Delete
      End If
    Next x
  Next srs

 

[Kaper]

so first line shall end with:

  ShowPercentage:=False, _
    ShowBubbleSize:=False

probably your pieChart object is (we can't see the assignment of an object to pieChart in earlier code).
If it is true, then add a line:

 pieChart.FullSeriesCollection(1).DataLabels.NumberFormat = "0;;"

You may first try in immediate window:

    ActiveSheet.ChartObjects("Chart 1").Chart.FullSeriesCollection(1).DataLabels.NumberFormat = "0;;"

and

    ActiveSheet.ChartObjects("Chart 1").Chart.FullSeriesCollection(1).DataLabels.NumberFormat = "0.0;;"

to make sure this approach works

 

[TheTiredEngineer]

so first line shall end with:

  ShowPercentage:=False, _
    ShowBubbleSize:=False

probably your pieChart object is (we can't see the assignment of an object to pieChart in earlier code).
If it is true, then add a line:

 pieChart.FullSeriesCollection(1).DataLabels.NumberFormat = "0;;"

You may first try in immediate window:

    ActiveSheet.ChartObjects("Chart 1").Chart.FullSeriesCollection(1).DataLabels.NumberFormat = "0;;"

and

    ActiveSheet.ChartObjects("Chart 1").Chart.FullSeriesCollection(1).DataLabels.NumberFormat = "0.0;;"

to make sure this approach works

OK that worked. But then moving the data labels to left and right, hows that supposed to be done?

Heres what Im trying:

pieChart.datalabel.Position = (coords)

 

[Kaper]

As for trying ...
I stronly encourage to look for such details by experimenting with macro recording and analysing where in structure (object model) are given methods/parameters.

Ad rem:

You can use coordinates (case a) or do a relative movement (case b - probably this one would be better suited for you)

like (assuming your pieChart object is a chart, and you want to move label at first segment:

'case a
With pieChart.FullSeriesCollection(1).Points(1).DataLabel
' coordinates are measured from topleft corner in directions: right and down
    .Left = 100
   .Top = 50
End With

'case b
With pieChart.FullSeriesCollection(1).Points(1).DataLabel
   .Left = .Left - 10
End With

And extra info - may be will be useful.

swithing off leading lines (swithing on with True):

pieChart.FullSeriesCollection(1).HasLeaderLines = False

Checking the width (and similar way to check height) of your whole chart:

Debug.Print pieChart.ChartArea.Width

Here in version displaying results in Immediate Window.
Final comment: I strongly encourage/recommend using this window and breakpoints or line by line (key F8) step execution.

 

[TheTiredEngineer]

As for trying ...
I stronly encourage to look for such details by experimenting with macro recording and analysing where in structure (object model) are given methods/parameters.

Ad rem:

You can use coordinates (case a) or do a relative movement (case b - probably this one would be better suited for you)

like (assuming your pieChart object is a chart, and you want to move label at first segment:

'case a
With pieChart.FullSeriesCollection(1).Points(1).DataLabel
' coordinates are measured from topleft corner in directions: right and down
    .Left = 100
   .Top = 50
End With

'case b
With pieChart.FullSeriesCollection(1).Points(1).DataLabel
   .Left = .Left - 10
End With

And extra info - may be will be useful.

swithing off leading lines (swithing on with True):

pieChart.FullSeriesCollection(1).HasLeaderLines = False

Checking the width (and similar way to check height) of your whole chart:

Debug.Print pieChart.ChartArea.Width

Here in version displaying results in Immediate Window.
Final comment: I strongly encourage/recommend using this window and breakpoints or line by line (key F8) step execution.

good advice about the record macro. Thanks I got it working!

 

[TheTiredEngineer]

I got it working initially but it broke and datalabels refuse to align to the left. Is there a way to create a textbox and put those datalabels into the textbox to make them easier to align?

 

[Kaper]

As for alignment - double test the sequence of actions and proper calling in objects hiererchy.

Textbox for data labels - well, it is possible, but you will have to take full responsibility for their behaviour, while standard labels are "managed" by the chart itself.
Moreover it is a lot of work to calculate their locations, insert them etc. So my suggestion is to do it standard way.