CODEWITHSUNDEEP
excelvbaexcel-2007excel-charts
Ivett0606
Ivett0606

Reputation: 21

Label the last value in chart

I was trying to create a VBA in Excel to avoid the probem of last labelled value in progress chart. I have several chart in a Dashboard and every time I update the progress with the new value I have to select the last value and add a label to the line chart. I already find a way to remove all last labels in one go, now the challenge is to add the lable to the last value.

To remove all the labels I was using the following code:

Sub RemoveDataLabelsFromEightCharts()


    Dim vChartNames As Variant
    Dim vChartName As Variant
    Dim wksChart As Worksheet
    
    Set wksChart = ActiveSheet
    
    vChartNames = Array("Chart 1", "Chart 2", "Chart 3", "Chart 4")
    For Each vChartName In vChartNames
        wksChart.ChartObjects(vChartName).Chart.SetElement msoElementDataLabelNone
    Next vChartName
End Sub

Also I found one which works if I select the chart to add the lable, in this case also it adds to the bar chart which is not needed.

Sub LastPointLabel()

  Dim mySrs As Series
  Dim iPts As Long
  Dim vYVals As Variant
  Dim vXVals As Variant

  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again.", vbExclamation
  Else
    Application.ScreenUpdating = False
    For Each mySrs In ActiveChart.SeriesCollection
      With mySrs
        vYVals = .Values
        vXVals = .XValues
 
  
       .HasDataLabels = False
        For iPts = .Points.Count To 1 Step -1
          If Not IsEmpty(vYVals(iPts)) And Not IsError(vYVals(iPts)) _
              And Not IsEmpty(vXVals(iPts)) And Not IsError(vXVals(iPts)) Then
    
            mySrs.Points(iPts).ApplyDataLabels _
                ShowSeriesName:=False, _
                ShowCategoryName:=False, ShowValue:=True, _
                AutoText:=True, LegendKey:=False
            Exit For
          End If
        Next
      End With
    Next
    ActiveChart.HasLegend = False
    Application.ScreenUpdating = True
  End If
End Sub

Upvotes: 2

Views: 476

Answers (1)

FunThomas
FunThomas

Reputation: 29296

(a) Instead of hardcoding ActiveChart, pass the chart as parameter to the routine that sets the label. You have already a routine that loops over all charts of interest, so you can use that:

Sub RemoveDataLabelsFromEightCharts()
    Dim vChartNames As Variant
    Dim vChartName As Variant
    Dim wksChart As Worksheet
    Set wksChart = ActiveSheet
    
    vChartNames = Array("Chart 1", "Chart 2", "Chart 3", "Chart 4")
    For Each vChartName In vChartNames
        LastPointLabel wksChart.ChartObjects(vChartName).Chart 
    Next vChartName
End Sub

Now all you have to do is declare the chart as parameter and use that instead of ActiveChart.

(b) If you want to have the label only on the line, check the Type-property of the series.

Sub LastPointLabel(chart As chart)
    Dim mySrs As Series
    Dim iPts As Long
    Dim vYVals As Variant
    Dim vXVals As Variant
    
    chart.SetElement msoElementDataLabelNone
    
    For Each mySrs In chart.SeriesCollection
        If mySrs.Type = xlLine Then    ' Check for "Line"-type
            vYVals = mySrs.Values
            vXVals = mySrs.XValues
    
            mySrs.HasDataLabels = False
            For iPts = mySrs.Points.Count To 1 Step -1
               If Not IsEmpty(vYVals(iPts)) And Not IsError(vYVals(iPts)) _
                     And Not IsEmpty(vXVals(iPts)) And Not IsError(vXVals(iPts)) Then
       
                   mySrs.Points(iPts).ApplyDataLabels _
                       ShowSeriesName:=False, _
                       ShowCategoryName:=False, ShowValue:=True, _
                       AutoText:=True, LegendKey:=False
                   Exit For
               End If
           Next
       End If
    Next
    chart.HasLegend = False
End Sub

Upvotes: 1

Related Questions