VBA Change Data Labels on a Stacked Column chart f

Question

I would like to change the data labels on a stacked column chart from 'value' to 'series name'.

The chart I want to edit:

I have searched the best I could on the internet and the only way I think this can be done is by using VBA. I am still a VBA noob so any help would be appreciated

Answer 1

Going into Excel, I select the chart I want to edit and then select all labels by going to Chart Tools > Add Chart Element > Data Labels > More Data Label Options

Next I uncheck whatever options I don't want in my labels, and check those I do want, under the "Label Options" in the dialog I just opened.

This works fine for me, but if it doesn't for you, or you for some other reason want to edit the labels via VBA, this script snippet sets the labels of a given chart to the series name:

Option Explicit

Sub Macro1()
  With ThisWorkbook.Worksheets("Grafar, utvikling").ChartObjects("Graf3").Chart.FullSeriesCollection(2)
    If Not .HasDataLabels Then
      .ApplyDataLabels
    End If
    If Not .DataLabels.ShowSeriesName Then
      .DataLabels.ShowSeriesName = True
    End If
  End With
End Sub

Clearly you need to switch the worksheetnames, chart-name, etc. to whatever you have in your chart - it shouldn't be very hard to figure out, but if you have any trouble feel free to comment on the question if you do have any problems.

I created the macro by using the macro recorder, and then editing the results, it can often give a good starting point for whatever task you attempt to do. In my experience it can be easier to figure out the object names / structures in Excel-VBA this way, than trying to figure it out from scratch.

Considering the above doesn't do what you want, I suspect that each column in your chart is one series. To quickly display the SeriesName for each dataseries in the chart, try looping over all the series in the chart:

Sub Macro1()
  Dim se As Series

  For Each se In ThisWorkbook.Worksheets("Grafar, utvikling").ChartObjects("Graf3").Chart.SeriesCollection
    If Not se.HasDataLabels Then
      se.ApplyDataLabels
    End If
    If Not se.DataLabels.ShowSeriesName Then
      se.DataLabels.ShowSeriesName = True
    End If
    If se.DataLabels.ShowValue Then
      se.DataLabels.ShowValue = False
    End If
  Next se

End Sub