chzhd4life
New Member
- Joined
- Jan 6, 2020
- Messages
- 13
- Office Version
- 365
- Platform
- Windows
Trying to automatically (e.g., not manually move one at a time) add labels to the outside right of stacked column chart. Found this old thread with a macro I'm attempting to use but getting an error and don't know enough about macros to figure it out! Here's macro:
Sub Stack()
Dim s As Series, yv, dlt!, lb As DataLabel, i%, j%, ch As Chart, cn
Const adj = -5 ' fine tuning
cn = Array("Chart1") ' desired charts
For j = LBound(cn) To UBound(cn)
Set ch = ActiveSheet.ChartObjects(cn(j)).Chart
If ch.ChartType = 52 Then ' stacked
ch.ChartGroups(1).GapWidth = 100
For i = 1 To ch.SeriesCollection.Count ' all series
Set s = ch.SeriesCollection(i)
yv = s.Values
dlt = (ch.ChartArea.Width / (UBound(yv) * 2)) + adj ' amount of horizontal change
If Not s.HasDataLabels Then s.HasDataLabels = True
For Each lb In s.DataLabels
lb.Left = lb.Left + dlt ' adjust position
Next lb, i
End If
Next
End Sub
Error I get is "The item with the specified name isn't found" and when I debug it lands here: Set ch = ActiveSheet.ChartObjects(cn(j)).Chart
Any ideas GREATLY appreciated!
Sub Stack()
Dim s As Series, yv, dlt!, lb As DataLabel, i%, j%, ch As Chart, cn
Const adj = -5 ' fine tuning
cn = Array("Chart1") ' desired charts
For j = LBound(cn) To UBound(cn)
Set ch = ActiveSheet.ChartObjects(cn(j)).Chart
If ch.ChartType = 52 Then ' stacked
ch.ChartGroups(1).GapWidth = 100
For i = 1 To ch.SeriesCollection.Count ' all series
Set s = ch.SeriesCollection(i)
yv = s.Values
dlt = (ch.ChartArea.Width / (UBound(yv) * 2)) + adj ' amount of horizontal change
If Not s.HasDataLabels Then s.HasDataLabels = True
For Each lb In s.DataLabels
lb.Left = lb.Left + dlt ' adjust position
Next lb, i
End If
Next
End Sub
Error I get is "The item with the specified name isn't found" and when I debug it lands here: Set ch = ActiveSheet.ChartObjects(cn(j)).Chart
Any ideas GREATLY appreciated!