Waterfall Chart Automated

Private Sub RefreshDataLabels_btn_Click()
  Call Main.ChartLabelsRefresh
End Sub

Sub ChartLabelsRefresh()

  Dim Sht As Worksheet
  Dim X As Long
  Dim iCnt As Long
  Dim Y As Long
  Dim idx() As Integer
  Dim R As Range
  Dim Cel As Range
  Dim Chrt As ChartObject
  Dim SC As Series
  Dim aChrt As ChartObject
  Dim PrevCel As Range
 
 
  Set Sht = ActiveSheet
  Sht.Unprotect               'I have the sheet protected without a password
                              'so people don't edit the wrong area
  Set PrevCel = ActiveCell    'Store the selected cells
 
  iCnt = Range("ICount").Value                          'Count of points on Waterfall Chart
  ReDim idx(1 To iCnt)                                  'Index to store which series a point belongs
  Set Cel = Range("Costs_hdr")
  Set R = Range(Cel.Offset(1, 0), Cel.Offset(iCnt, 0))
  X = 0
  For Each Cel In R                                      'Get each value and determine which series
    X = X + 1
    If X = 1 Or X = iCnt Then                            'First point or Last Point belong to series 2
      idx(X) = 2
    ElseIf Cel.Value < 0 Then                            'Negative points belong to series 4
      idx(X) = 4
    ElseIf Cel.Value > 0 Then                            'Positive points belong to series 3
      idx(X) = 3
    End If
  Next Cel
 
                  'You will have to change this based on your chart name
  Sht.ChartObjects("Chart 3").Activate
 
  'Delete all datalabels before adding them back in
  For X = 2 To 4
    Set SC = ActiveChart.SeriesCollection(X)
    If SC.HasDataLabels = True Then
      SC.DataLabels.Delete
    End If
  Next X
 
  'Add datalabels for each series
  'Delete series datalabels from non-related points
  'Use idx() to check wich series each point belongs
  For Y = 2 To 4
    Set SC = ActiveChart.SeriesCollection(Y)
    SC.ApplyDataLabels ShowValue:=True
    SC.HasDataLabels = True
    
    With SC.DataLabels.Format.TextFrame2.TextRange.Font.Fill
      .Visible = msoTrue
      .ForeColor.RGB = RGB(0, 0, 0)
      .Transparency = 0
      .Solid
    End With
    
    For X = 1 To iCnt
      If idx(X) <> Y Then
        SC.Points(X).DataLabel.Delete
      End If
    Next X
  Next Y
 
  'Resize fonts based on the number of axes
  Select Case iCnt
    Case Is > 16
      ActiveChart.Axes(xlCategory).TickLabels.Font.Size = 7
    Case Is > 13
      ActiveChart.Axes(xlCategory).TickLabels.Font.Size = 9
    Case Is > 10
      ActiveChart.Axes(xlCategory).TickLabels.Font.Size = 11
    Case Is > 7
      ActiveChart.Axes(xlCategory).TickLabels.Font.Size = 12
    Case Is <= 7
      ActiveChart.Axes(xlCategory).TickLabels.Font.Size = 13
  End Select
 
  'Make the sheet protected again
  With Sht
    .EnableOutlining = True
    .Protect DrawingObjects:=False, Contents:=True, Scenarios:=True _
      , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
      AllowFormattingRows:=True, AllowInsertingHyperlinks:=True, AllowFiltering _
    :=True, userInterfaceOnly:=True
  End With
 
  'Select the cells the user had selected
  PrevCel.Select
    
    
    
End Sub