Setting up Two Pie charts in VBA

[TheTiredEngineer]

Im trying to setup two pie charts at the same time and Ive got the first pie chart to delete unnecessary labels but then the second one keeps crashing the vba. This all started when I added a second loop to adjust the first slice angle of the second chart to be 360 minus the angle of the first pie. Ive tried breakpoints and using debug.Print to find the issue and it just says

Heres my code:

 Dim chartObj As ChartObject
    Dim chartObj2 As ChartObject
    Dim pieChart As Chart
    Dim pieChart2 As Chart
    Dim ws As Worksheet
    Dim angleCell As Range
    Dim angleValue As Integer
    Dim angleValue2 As Integer
    Dim srs As Series
    Dim srs1 As Series
    Dim x As Integer
    Dim y As Integer

    ' Set the worksheet and the cell to monitor for angle changes
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Adjust the sheet name
    Set angleCell = ws.Range("$C$136") ' Specify the cell where the angle will be input

    ' Check if the changed cell is the one we're monitoring
    If Not Intersect(Target, angleCell) Is Nothing Then
        ' Get the new angle value from the cell
        angleValue = angleCell.Value
        
        ' Ensure the value is between 0 and 360
        If angleValue < 0 Or angleValue > 360 Then
            MsgBox "Please enter a value between 0 and 360."
            Exit Sub
        End If
        
        ' Set the chart object to "Chart 1"
        On Error Resume Next ' Prevent errors if the chart is not found
        Set chartObj = ws.ChartObjects("SketchChart1")
        Set chartObj2 = ws.ChartObjects("SketchChart2")
        
        On Error GoTo 0 ' Resume normal error handling
        
        ' Check if the chart exists and is a pie or donut chart
        If Not chartObj Is Nothing Then
            Set pieChart = chartObj.Chart
            If pieChart.ChartType = xlPie Or pieChart.ChartType = xlDoughnut Then
               pieChart.ChartGroups(1).FirstSliceAngle = angleValue
                pieChart.FullSeriesCollection(1).DataLabels.NumberFormat = "0;;"
                pieChart.FullSeriesCollection(1).HasLeaderLines = False
                
                Debug.Print "First Slice Angle set to " & angleValue & " for Chart 1"
                
            Else
                MsgBox "Chart 1 is not a pie or donut chart."
            End If
        Else
            MsgBox "Chart 1 not found."
        End If
         
        If Not chartObj2 Is Nothing Then
        Set pieChart2 = chartObj2.Chart
            If Mirror >= 2 Then
                angleValue2 = 360 - angleValue
                pieChart2.ChartGroups(1).FirstSliceAngle = angleValue2
                pieChart2.FullSeriesCollection(1).DataLabels.NumberFormat = "0;;"
                pieChart2.FullSeriesCollection(1).HasLeaderLines = False
                Debug.Print "First Slice Angle set to " & angleValue & " for Chart 1 & Chart 2"
            End If
        
        End If
        
   'Set Threshold
    Threshold = 0
    
    'Reapply Data Labels (Refreshing)
    pieChart.ApplyDataLabels _
    ShowValue:=False, _
    AutoText:=True, _
    LegendKey:=False, _
    HasLeaderLines:=False, _
    ShowSeriesName:=False, _
    ShowCategoryName:=True, _
    ShowPercentage:=False, _
    ShowBubbleSize:=False

'Loop through each data label in Pie Chart 1 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
  
  'Loop through each data label in Pie Chart 2 for threshold
  For Each srs1 In pieChart2.SeriesCollection
    For y = 1 To UBound(srs1.Values)
      If Abs(srs1.Values(y)) = Threshold Then
        srs1.Points(y).DataLabel.Delete
      End If
    Next y
  Next srs1
  
End If

 

[DanteAmor]

When it appears the second time, it no longer finds the label to delete.
Although it is a way that I don't like, but I didn't find a way to check if the label exists or doesn't exist. Then try the following:

'Loop through each data label in Pie Chart 1 for threshold
  For Each srs In pieChart.SeriesCollection
    For x = 1 To UBound(srs.Values)
      If Abs(srs.Values(x)) = Threshold Then
        On Error Resume Next
        srs.Points(x).DataLabel.Delete
        On Error GoTo 0
      End If
    Next x
  Next srs
  
  'Loop through each data label in Pie Chart 2 for threshold
  For Each srs1 In pieChart2.SeriesCollection
    For y = 1 To UBound(srs1.Values)
      If Abs(srs1.Values(y)) = Threshold Then
        On Error Resume Next
        srs1.Points(y).DataLabel.Delete
        On Error GoTo 0
      End If
    Next y
  Next srs1

 

[TheTiredEngineer]

When it appears the second time, it no longer finds the label to delete.
Although it is a way that I don't like, but I didn't find a way to check if the label exists or doesn't exist. Then try the following:

'Loop through each data label in Pie Chart 1 for threshold
  For Each srs In pieChart.SeriesCollection
    For x = 1 To UBound(srs.Values)
      If Abs(srs.Values(x)) = Threshold Then
        On Error Resume Next
        srs.Points(x).DataLabel.Delete
        On Error GoTo 0
      End If
    Next x
  Next srs
 
  'Loop through each data label in Pie Chart 2 for threshold
  For Each srs1 In pieChart2.SeriesCollection
    For y = 1 To UBound(srs1.Values)
      If Abs(srs1.Values(y)) = Threshold Then
        On Error Resume Next
        srs1.Points(y).DataLabel.Delete
        On Error GoTo 0
      End If
    Next y
  Next srs1

Is there a way to merge the codes together? It should be looping through different collections and values, so I dont understand why it no longer finds the labels.

 

[DanteAmor]

I already saw in your code where the labels are reset:

    pieChart.ApplyDataLabels _
      ShowValue:=False, _
      AutoText:=True, _
      LegendKey:=False, _
      HasLeaderLines:=False, _
      ShowSeriesName:=False, _
      ShowCategoryName:=True, _
      ShowPercentage:=False, _
      ShowBubbleSize:=False

But you missed adding the code for pieChart2, so :

    'Reapply Data Labels (Refreshing)
    pieChart.ApplyDataLabels _
    ShowValue:=False, _
    AutoText:=True, _
    LegendKey:=False, _
    HasLeaderLines:=False, _
    ShowSeriesName:=False, _
    ShowCategoryName:=True, _
    ShowPercentage:=False, _
    ShowBubbleSize:=False

    pieChart2.ApplyDataLabels _
      ShowValue:=False, _
      AutoText:=True, _
      LegendKey:=False, _
      HasLeaderLines:=False, _
      ShowSeriesName:=False, _
      ShowCategoryName:=True, _
      ShowPercentage:=False, _
      ShowBubbleSize:=False

'Loop through each data label in Pie Chart 1 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
 
  'Loop through each data label in Pie Chart 2 for threshold
  For Each srs1 In pieChart2.SeriesCollection
    For y = 1 To UBound(srs1.Values)
      If Abs(srs1.Values(y)) = Threshold Then
        srs1.Points(y).DataLabel.Delete
      End If
    Next y
  Next srs1

 

[TheTiredEngineer]

Awesome, thanks for catching that!