Excel VBA Code for data label position

[adnan1975]

I have problem with the data label position in the following code. It works fine for updating chart and showing the data labels in the center but I want data labels on top of bars. The code gives me error "Method 'Position' of object 'DataLabels' failed"

lcol = INV.Range("N" & 4).End(xlToLeft).Column


Set RngSOL = INV.Range(INV.Cells(4, lcol), INV.Cells(9, lcol))
Set rngsolx = INV.Range("B4", "B9")


End With


Set Chart3 = db.ChartObjects("Chart 6").Chart


        With Chart3
            .ChartType = xlColumnStacked
            .SetSourceData Source:=rngsolx


            With .SeriesCollection.NewSeries
                        
                        With Chart3.SeriesCollection(1)
                        .Values = RngSOL
                        .XValues = rngsolx
                        .ApplyDataLabels
                        .DataLabels.ShowValue = True
                        .DataLabels.Position = xlLabelPositionAbove
                        .Interior.Color = RGB(0, 112, 192)
                        End With
                        
                 
            End With


        End With
End Sub

I am not sure what i am doing wrong. Any help is much appreciated.

 

[NdNoviceHlp]

Here's an example. You need to add the all the data labels, then format them all, then remove the ones U don't want. HTH. Dave

Db.ChartObjects("Chart 6").Chart.SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowValue, _
        AutoText:=True, LegendKey:=False

With Db.ChartObjects("Chart 6").Chart.SeriesCollection(1).DataLabels
.Position = xlLabelPositionAbove
.Orientation = xlHorizontal
.AutoScaleFont = False
.Font.Size = 13
.Font.Bold = True
.Font.Italic = True
.NumberFormat = "$#,##0.00"
End With

'e.g. to remove the first data label from series 1
Db.ChartObjects("Chart 6").Chart.SeriesCollection(1).Points(1).DataLabel.Delete

 

[adnan1975]

Here's an example. You need to add the all the data labels, then format them all, then remove the ones U don't want. HTH. Dave

Db.ChartObjects("Chart 6").Chart.SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowValue, _
        AutoText:=True, LegendKey:=False

With Db.ChartObjects("Chart 6").Chart.SeriesCollection(1).DataLabels
.Position = xlLabelPositionAbove
.Orientation = xlHorizontal
.AutoScaleFont = False
.Font.Size = 13
.Font.Bold = True
.Font.Italic = True
.NumberFormat = "$#,##0.00"
End With

'e.g. to remove the first data label from series 1
Db.ChartObjects("Chart 6").Chart.SeriesCollection(1).Points(1).DataLabel.Delete

Thanks. for some reason this line doesnt work

.Position = xlLabelPositionAbove

if i change Above with Center then it works. It gives me the same error as I mentioned in my post. Not sure if this is a feature of Excel 2013. I cannot also find option to show data labels above the bar in the label properties. I don't know why it is happening though.

 

[NdNoviceHlp]

I trialled it out with a chart on sheet 1 in XL 2016... works as advertised. Not sure why you're having difficulties? Dave

Sheets("sheet1").ChartObjects(1).Chart.SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowValue, _
        AutoText:=True, LegendKey:=False
With Sheets("sheet1").ChartObjects(1).Chart.SeriesCollection(1).DataLabels
.Position = xlLabelPositionAbove
.Orientation = xlHorizontal
.AutoScaleFont = False
.Font.Size = 13
.Font.Bold = True
.Font.Italic = True
.NumberFormat = "$#,##0.00"
End With

'e.g. to remove the first data label from series 1
Sheets("sheet1").ChartObjects(1).Chart.SeriesCollection(1).Points(1).DataLabel.Delete

 

[Boojum_1965]

I know this thread is from a little way back but I've been having the same issue. I think the problem is that not all Label Position enums are available for all charts. If you select 'Format Data Labels' using the right-click context menu on a label, the properties pane on the right hand side only has 'Centre', 'Inside End' and 'Inside Base' for column charts (for example).

As I want to move a column label above the column I suspect I'm going to have to move it to an absolute position .