CODEWITHSUNDEEP
excelvbachartsformatting
Dan Sutton
Dan Sutton

Reputation: 63

Pie Chart VBA DataLabel Formatting

Evening all,

I have been stuck for a few hours trying to overcome an issue i am facing with formatting charts using VBA.

So i recorded the following macro based on updating a chart to reflect the formats i want.. the issue is that when i then run this macro again i get "Method 'Select' of object 'Datalabels' failed"

Sub UpdateChartFormat()
   ActiveSheet.ChartObjects("Chart 4").Activate
   ActiveChart.SeriesCollection(1).DataLabels.Select
   Selection.ShowPercentage = True
   Selection.Separator = "" & Chr(10) & ""
   ActiveSheet.ChartObjects("Chart 1").Activate
   ActiveChart.SeriesCollection(1).DataLabels.Select
   Selection.ShowPercentage = True
   Selection.ShowValue = False
   ActiveSheet.Range("D32").Select
   End Sub

I would really appreciate some help with this, have not been able to find the fix elsewhere.

I'm thinking it might have to do with the series collection as well because the charts are dynamic and the series name changes based on user selection.

Ideally, i would like to run this as part of another larger macro without activating the charts if that is possible.

Thanks in advance!

EDIT:

So this now runs the code without any errors but doesn't actually update the formatting XD

Sub UpdateChartFormat()

With ActiveWorkbook.Sheets("MHFA Summary").ChartObjects("Chart 4").Activate
   With ActiveChart.SeriesCollection(1).DataLabels _
       .ShowPercentage = True
   With ActiveChart.SeriesCollection(1).DataLabels _
       .Separator = "" & Chr(10) & ""
   End With
   End With
End With
With ActiveWorkbook.Sheets("MHFA Summary").ChartObjects("Chart 1").Activate
   With ActiveChart.SeriesCollection(1).DataLabels _
       .ShowPercentage = True
   With ActiveChart.SeriesCollection(1).DataLabels _
       .Separator = "" & Chr(10) & ""
   End With
   End With
End With

End Sub

Upvotes: 0

Views: 3147

Answers (2)

Dan Sutton
Dan Sutton

Reputation: 63

Managed to create a loop using the following code that updates the DataLabels format to how I wanted it by going through each point.

Sub FormatDataLabels()
Dim intPntCount As Integer

ActiveSheet.ChartObjects("Chart 4").Activate
With ActiveChart.SeriesCollection(1)
    For intPntCount = 1 To .Points.Count
        .Points(intPntCount).ApplyDataLabels _
            AutoText:=False, ShowSeriesName:=False, ShowCategoryName:=False, _
            ShowValue:=True, ShowPercentage:=True, Separator:="" & Chr(10) & ""
    Next intPntCount
End With
ActiveSheet.ChartObjects("Chart 1").Activate
    With ActiveChart.SeriesCollection(1)
    For intPntCount = 1 To .Points.Count
        .Points(intPntCount).ApplyDataLabels _
            AutoText:=False, ShowSeriesName:=False, ShowCategoryName:=False, _
            ShowValue:=False, ShowPercentage:=True, Separator:="" & Chr(10) & ""
    Next intPntCount
End With
End Sub

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166126

Something like this: 

Sub UpdateChartFormat()

   With ActiveSheet.ChartObjects("Chart 4")
       .Activate
       With .Chart.SeriesCollection(1).DataLabels
           .ShowPercentage = True
           .Separator = "" & Chr(10) & ""
       End With
   End With

   With ActiveSheet.ChartObjects("Chart 1")
        .Activate
        With .Chart.SeriesCollection(1).DataLabels
            .ShowPercentage = True
            .ShowValue = False
        End With
   End With

End Sub

According to https://learn.microsoft.com/en-us/office/vba/api/excel.datalabels.showpercentage

Remarks The chart must first be active before you can access the data labels programmatically or a run-time error will occur.

Upvotes: 1

Related Questions