Adding Data Labels

It would be nice to be able to specify a range of text to use as your data labels
You need to add the data labels and then manually edit each one ??
adding data labels to a scatter or bubble chart.
If you have a Pie Chart and the data labels are not wide enough to display all the data on one line then a possible work around is to make the chart area a lot bigger but to keep the Plot Area the same size.


Public Sub Chart_AddLabels() 
Dim schartname As String
   schartname = Right(ActiveChart.Name, Len(ActiveChart.Name) - Len(ActiveSheet.Name) - 1)
   Set rgerange = Application.InputBox("Select the labels to add", Type:=8)
   snewlabel = rgerange.Address
   ActiveSheet.ChartObjects(schartname).Activate
   Call Chart_DataLabelsAdd(1, rgerange.Column, _
                               rgerange.Row, _
                               rgerange.Row + rgerange.Rows.Count - 1)
End Sub

Public Sub Chart_DataLabelsAdd(ByVal iSeriesNo As Integer, _ 
                               ByVal iColFirst As Integer, _
                               ByVal lRowFirst As Long, _
                               ByVal lRowLast As Long)
Dim rgerange As Range
Dim lcount As Long
Dim snewlabel As String
Dim lsmallest As Long
   ActiveChart.SeriesCollection(iSeriesNo).ApplyDataLabels Type:=xlDataLabelsShowLabel
   If ActiveChart.SeriesCollection(iSeriesNo).DataLabels.Count <= (lRowLast - lRowFirst + 1) Then
      lsmallest = ActiveChart.SeriesCollection(1).DataLabels.Count
   End If
   If (lRowLast - lRowFirst) <= ActiveChart.SeriesCollection(1).DataLabels.Count Then
      lsmallest = lRowLast - lRowFirst + 1
   End If
   For lcount = 1 To lsmallest
      snewlabel = ActiveSheet.Cells(lRowFirst + lcount - 1, iColFirst).Value
      ActiveChart.SeriesCollection(iSeriesNo).DataLabels.Select
      ActiveChart.SeriesCollection(iSeriesNo).Points(lcount).DataLabel.Select
      Selection.Characters.Text = snewlabel
   Next lcount
End Sub


© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext