Excel VBA Code for data label position

adnan1975

New Member
Joined
Aug 24, 2017
Messages
25
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"

Code:
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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,791
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
Code:
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

New Member
Joined
Aug 24, 2017
Messages
25
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
Code:
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

Well-known Member
Joined
Nov 9, 2002
Messages
2,791
I trialled it out with a chart on sheet 1 in XL 2016... works as advertised. Not sure why you're having difficulties? Dave
Code:
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

New Member
Joined
Feb 24, 2011
Messages
1
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 :(.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,913
Messages
5,655,919
Members
418,253
Latest member
TheJackal26

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top