VBA Chart Data Labels msoNumberFormat for Percentage with decimals

[briskie]

How do you tell msoChartFieldPercentage to have the Number Format "0,00%"?

Please Login or Register  to view this content.

How do you integrate this line to the code:
msoNumberFormat = "0,00%"

[Marius44]

HI
I think it's better to attach the file.
But why not publish the request in the Charts section?
HI,
Mario

[Andy Pope]

Please Login or Register  to view this content.

[briskie]

Hi Mario, Hi Andy,

Andy, thank you for your response! I tried the line, but it changes the format of the Category Value.
As Mario suggested, I have attached the file – could you please have a look at the code?

Thank you very much!
Toni

[Andy Pope]

Please Login or Register  to view this content.

You did not mention the data labels where compound labels.

[Marius44]

Thanks Andy
I allowed myself to make only two changes (the ones colored in red below) but the percentages seem wrong to me. Only in the first label I get 100.00% while the others are all 0.00%

Sub xx()
Dim p As Point
Dim CatValueLength As Variant
Dim dls As DataLabels
Dim length As Long
Dim labelItems As Variant

ActiveSheet.ChartObjects(1).Activate
With ActiveChart.SeriesCollection(1)
.HasDataLabels = True
With .DataLabels
.ShowValue = True
.ShowCategoryName = True
.ShowPercentage = True
.Separator = vbLf
.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = vbBlack
.Format.TextFrame2.TextRange.Font.Bold = False
.NumberFormat = "0.0000"
.Position = xlLabelPositionOutsideEnd
End With
For Each p In .Points
labelItems = Split(p.DataLabel.Text, vbLf)
With p.DataLabel.Format.TextFrame2.TextRange
.Text = labelItems(0)
.Text = .Text & vbLf & Format(labelItems(1), "0.00")
.Text = .Text & vbLf & Format(labelItems(2), "0.00%")
'~~> Color the category name
length = Len(labelItems(0)) + Len(labelItems(1))
.Characters(1, length).Font.Bold = True
.Characters(1, length).Font.Fill.ForeColor.RGB = p.Format.Fill.ForeColor.RGB
End With
Next
End With
End Sub

Did I do something wrong?

HI,
Mario

[Andy Pope]

I get percentages as expected.

Are you using regional settings? In which case perhaps the values have a , instead of period for decimal place. If so you will need to replace the comma with a period in labelItems(1) and lableItems(2) before formatting as number

[Marius44]

Hi

No, I'm using system separators.
But what is not clear to me is why it sets the first percentage equal to 100.00 and the others all to 0.00

HI,
Mario

[briskie]

Hi Andy,

stunning!

So compound labels they are. So much to learn.

Mario, as Andy says, it seems to be something regional.
Try to replace the "." with a "," (.NumberFormat = "0,0000"), this worked for me.

Andy, if I may, two questions:

1.
I added these lines to the end, as an option to have all three label lines in the same color:

.Characters(8, length).Font.Bold = True ' by Toni
.Characters(8, length).Font.Fill.ForeColor.RGB = p.Format.Fill.ForeColor.RGB ' by Toni
.Characters(12, length).Font.Bold = False ' by Toni
.Characters(12, length).Font.Fill.ForeColor.RGB = p.Format.Fill.ForeColor.RGB ' by Toni

The first two lines work as expected, the last two don't. I can only go as far as "12". "13" is already an "invalid procedure".
I also tried Mario's red "+Len(labelItems(1))". This does the job of formatting the value like the category.
But unfortunately, it also formats the first two digits of the percentage?
What am I missing?

2.
The "original" code had a line, pointing to the cells the formatting should follow.
I could easily choose, whether to follow the category font colors or the value font colors:

c = ActiveSheet.Cells(1, i + 1).Font.Color
or
c = ActiveSheet.Cells(2, i + 1).Font.Color

But I can't work out, where to make that switch in your code.
Would you please be so kind, to point at it?

Thank you so much!

[Andy Pope]

Load the data label with text first, then format the bits of text

Please Login or Register  to view this content.

You need to include the length of vbLF, which is the new line.

[Marius44]

Hello everyone
Never give up!
The previous macro works if in the first formatting I put a comma and in the other two I put a point.
I mistakenly always put either full points or commas.
Thank you.

HI, Mario

[briskie]

Hello Andy, hello Mario.

Andy, wow. This is so neat. Thank you so much!

Mario, same case here with the dots and commas.

What a life changer

Thank you very, very much!

[briskie]

Hello Andy,

might there also be a way to use cells in a column (instead of a row)?

In your code, these lines point to the cell in a row to be used:

categoryColorRow = 1
valueColorRow = 2
colIndex = 2

Is there a way to choose a column instead?
The code I was using up until now had a range setting, where you could either choose a horizontal or vertical row as the input for the labels:

Please Login or Register  to view this content.

Once again, I'm trying to combine with no success.