Data series labels VB

lashutm

New Member
Joined
Aug 7, 2007
Messages
10
oChart.SeriesCollection(1).DataLabels.Font.FontStyle = "Bold"

I was able to make my data label series bold with the code above, but how do I move them up in a chart? And perhaps, add percentages? Using a macro recorder has not helped me in my quest because I have a macro that is in access and throws data into Excel to make the graph.

Thanks!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
I mentioned this in the other thread. You use the macro recorder to see the syntax, not just to copy and paste code into another procedure.

Code:
oChart.SeriesCollection(1).DataLabels.Position = xlLabelPositionOutsideEnd

If that's not the position you need, check out the options in the XlDataLabelPositionClass in the VB Editor's Object Browser.

To show more than the default label contents, you have to build a string, either in a cell or in the VBA code. Then apply it to the label:

Code:
oChart.SeriesCollection(1).Points(1).DataLabel.Characters.Text = "abcde"

If you're working within Excel, there are a couple add-ins you could use to apply labels from worksheet cells to plotted points:

Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com
 
Upvote 0
For some reason I get the Run-time error '1004': application defined or object defined error when I throw in: oChart.SeriesCollection(1).DataLabels.Position = xlLabelPositionOutsideEnd

I guess that is what I'm trying to say. For some reason, lines of code such as that just won't work when they seemingly should.
 
Upvote 0
What's the chart type? Not all label positions are allowed for every chart type.
 
Upvote 0
xl3DColumnClustered.

This is my data series code:
oChart.SeriesCollection(1).Interior.ColorIndex = 2
oChart.SeriesCollection(2).Interior.ColorIndex = 10
oChart.SeriesCollection(3).Interior.ColorIndex = 11
oChart.SeriesCollection(4).Interior.ColorIndex = 8
oChart.SeriesCollection(1).HasDataLabels = True
oChart.SeriesCollection(2).HasDataLabels = True
oChart.SeriesCollection(3).HasDataLabels = True
oChart.SeriesCollection(4).HasDataLabels = True
oChart.SeriesCollection(1).DataLabels.Font.FontStyle = "Bold"
oChart.SeriesCollection(2).DataLabels.Font.FontStyle = "Bold"
oChart.SeriesCollection(3).DataLabels.Font.FontStyle = "Bold"
oChart.SeriesCollection(4).DataLabels.Font.FontStyle = "Bold"
oChart.SeriesCollection(1).DataLabels.VerticalAlignment = xlCenter
oChart.SeriesCollection(1).DataLabels.HorizontalAlignment = xlCenter

Now I just want to move them up a few millimeters to get them off the bar in the chart and I'll be set.

Why are the object parameters for VB so difficult to find and incorporate!?!? lol-- even the macro recorder proves to serve no purpose in this case. . .much less google.
 
Upvote 0
"xl3DColumnClustered" - one of the ugliest and least functional chart types known to man. It might look pretty to the untrained eye, or to the marketing staff, but it distorts the data with clutter and with false perspectives. It also does not provide much flexibility in terms of label positioning; in fact, the Position property is undefined for data labels in this chart type.

Do yourself and your audience a favor and convert the chart to the 2D version, xlColumnClustered. Then what I have been suggesting will actually work.
 
Upvote 0
I sense disdain lol. xlColumnClustered it is. In fact, I don't even need to position the labels with this chart because of the default formatting.

Thank you very much for your help!
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

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
Back
Top