Updating Doughnut Chart Labels through VBA

Question

Updating Doughnut Chart Labels through VBA

Anonymous

See below for the code I am running. I want to add the Category names to the chart data labels. For some reason, it is only pulling ": " and not the category name in front of the semi-colon. Any ideas?

Sub UpdateDoughnutChartLabels()

On Error Resume Next 

Dim ws As Worksheet 

Dim chartObj As ChartObject 

Dim series As Series 

Dim point As Point 

Dim i As Long 

' Specify the worksheet where the doughnut charts are located 

Set ws = ThisWorkbook.Sheets("Asset Mix") 

' Loop through each chart object in the specified worksheet 

For Each chartObj In ws.ChartObjects 

    ' Loop through each series in the chart 

    For Each series In chartObj.Chart.SeriesCollection 

        ' Enable data labels for the entire series 

        series.HasDataLabels = True 

        ' Loop through each point in the series 

        For i = 1 To series.Points.Count 

            ' Check if the data label should be shown for non-zero values 

            If series.Points(i).DataLabel.Text &lt;&gt; "0" Then 

                ' Add category name to the data label text 

                Dim categoryName As String 

                categoryName = chartObj.Chart.Axes(xlCategory).CategoryNames(i) 

                series.Points(i).DataLabel.Text = categoryName & ": " & series.Points(i).DataLabel.Text 

                ' Customize additional formatting options here if needed 

            End If 

        Next i 

        ' Loop through each point again to set font properties 

        For Each point In series.Points 

            If point.DataLabel.Text &lt;&gt; "0" Then 

                With point.DataLabel.Format.TextFrame2.TextRange.Font 

                    .Name = "Calibri" 

                    .Size = 9 

                    ' Add other font properties if necessary 

                End With 

            End If 

        Next point 

    Next series 

Next chartObj 

' Reset error handling to default 

On Error GoTo 0 

' Additional debugging message 

MsgBox "Code completed successfully"

End Sub

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

0 additional answers

Answer 1

Hello Dom,

I'm Ibhadighi and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

The issue with your VBA code appears to be in the way you're trying to assign the category names to the data labels. The CategoryNames property might not be working as expected, or it's possible that the chart is not properly linked to the range that includes the category names. Here's a simplified correction to your code:

Make sure that the CategoryNames is properly referencing a range that contains your categories. If CategoryNames does not return the correct values, you might need to explicitly reference the range where the category names are stored.

Here's a simplified version of the section of your code that assigns the category names:

' ... ' Add category name to the data label text Dim categoryName As String ' Assuming that your category names are in the same row as your data labels ' and start from column 1, modify the range reference as needed. categoryName = ws. Cells(series. Points(i). DataLabel.Parent.Index, 1). Value series. Points(i). DataLabel.Text = categoryName & ": " & series. Points(i). DataLabel.Text ' ...

In this code snippet, replace `ws. Cells(series. Points(i). DataLabel.Parent.Index, 1). Value` with the correct reference to where your category names are located on the worksheet.

Also, make sure that the `DataLabel.Text` property is not read-only. In some cases, directly setting the `. Text` property doesn't work due to the way Excel handles data labels. You might need to use `. Caption` or `. Formula`, depending on your Excel version.

Lastly, ensure that your data labels are not already formatted to show a different kind of label (like percentage, value, etc.), which could be overriding your text.

Remember, if you are not sure about the properties and methods available or if they are functioning as expected, you can always use the macro recorder to record the steps you'd take to manually add the category names to the labels and then review the generated code for the correct syntax and methods.

I hope this helps.

Best Regards,
Ibhadighi

Share via

Updating Doughnut Chart Labels through VBA

0 additional answers