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!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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"
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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