VBA to create data labels with both a percentage and text

RRswim

New Member
Joined
Nov 14, 2011
Messages
2
Using VBA, I coded data labels onto a stacked bar graph. See the code below.

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(5).ApplyDataLabels
ActiveChart.SeriesCollection(5).Name = "Score"
ActiveChart.SeriesCollection(5).Values = Range(startData.Offset(0, 38), startData.Offset(2, 38))
ActiveChart.SeriesCollection(5).DataLabels.NumberFormat = "0%"

I want to add text to the end so that the data label gives the percentage and some text like 82%ile. The 82 value is calculated by the worksheet in the cell. I concatenated a value in another cell with "ile", but when the VBA selects those cells, I just get unformatted numbers without the text.

I am using Excel 2007 on a windows machine. I have searched for a couple of hours on the forums. I would appreciate any help. Thanks!
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Welcome to the Board,

Keeping your data range with just numeric values, you should be able to apply a custom format to the data labels....
Code:
ActiveChart.SeriesCollection(5).DataLabels.NumberFormat = "0%il\e"
 

RRswim

New Member
Joined
Nov 14, 2011
Messages
2
WoW! I search for hours on that- Thank you so much! Can you explain why I need the "\" between the l and the e? It totally worked though!
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
WoW! I search for hours on that- Thank you so much! Can you explain why I need the "\" between the l and the e? It totally worked though!

e is a special character for Scientific formatting. Placing the "\" before it indicates you want the literal e instead of the scientific notation.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,915
Members
413,952
Latest member
JGer

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