sjgrey
asked on
Formatting within a data series label is lost
I have a text string including two subscript characters that is created by some VBA code in a cell that a graph refers to for the label to be displayed for a data point.
The graph picks up the label characters but loses the subscripting. The percentile notation P90 appears as it is here rather than with the 90 being a subscript.
Is this something Excel just doesn't do or is there a way to force the label to include the subscript characters?
The graph picks up the label characters but loses the subscripting. The percentile notation P90 appears as it is here rather than with the 90 being a subscript.
Is this something Excel just doesn't do or is there a way to force the label to include the subscript characters?
ASKER
Thanks - I would just like to clarify a couple of points
1 There is just one chart on the sheet and the data series to be labelled, a single point, is the third one in the list in the Select Data dialog. Does this affect the numbers in brackets after ChartObjects, SeriesCollection and Points?
2 The string is actually longer than Pnn. It's general form is "Pnn=$nn.nM". Would ".DataLabel.Characters(2, 3).Font.Subscript = True" be the right syntax to pick them up?
I'll do some trial and error but it's nice to know how it should work.
1 There is just one chart on the sheet and the data series to be labelled, a single point, is the third one in the list in the Select Data dialog. Does this affect the numbers in brackets after ChartObjects, SeriesCollection and Points?
2 The string is actually longer than Pnn. It's general form is "Pnn=$nn.nM". Would ".DataLabel.Characters(2, 3).Font.Subscript = True" be the right syntax to pick them up?
I'll do some trial and error but it's nice to know how it should work.
If I understand you correctly, the syntax would be:
Brad
DataLabelSubscriptingQ26865514.xlsm
.DataLabel.Characters(2, 2).Font.Subscript = True
In the Characters property, the first value is the position of the starting character, while the second is the number of characters. Since you want two characters subscripted, the second parameter should be 2.Brad
DataLabelSubscriptingQ26865514.xlsm
To refer to different series, you would adjust statement 4 to:
You can refer to the chart by either an index number or name. Pick the method that is less confusing to you. In my sample workbook, the chart object is named "Chart 1" so I could have used the following in statement 3:
The entire macro then becomes:
DataLabelSubscriptingQ26865514.xlsm
With .SeriesCollection(3).Points(1) 'Refers to third series, first data point
You can refer to the chart by either an index number or name. Pick the method that is less confusing to you. In my sample workbook, the chart object is named "Chart 1" so I could have used the following in statement 3:
With ActiveSheet.ChartObjects("Chart 1").Chart
The entire macro then becomes:
Sub MarkerSubscript()
Dim s As String
With ActiveSheet.ChartObjects("Chart 1").Chart
With .SeriesCollection(3).Points(1)
s = Range("B2").Value
.ApplyDataLabels
.DataLabel.Text = s
.DataLabel.Characters(2, 2).Font.Subscript = True
End With
End With
End Sub
BradDataLabelSubscriptingQ26865514.xlsm
ASKER
It almost works. No matter what I do though, the whole label is formatted as subscript. I tried formatting the whole thing with subscript false to start and then applying subscript to the two digits but that doesn't work either.
If I comment out the line with Subscript = True the label is there in normal sized characters. If I leave it in as above the whole label goes to subscript. Is this an obvious glitch?
Sub SetLabel()
Dim s As String
With ActiveSheet.ChartObjects(1).Chart
With .SeriesCollection(3).Points(1)
s = Range("N21").Value
.ApplyDataLabels
.DataLabel.Text = s
.DataLabel.Characters(1, Len(s)).Font.Subscript = False
.DataLabel.Characters(2, 2).Font.Subscript = True
End With
End With
End Sub
If I comment out the line with Subscript = True the label is there in normal sized characters. If I leave it in as above the whole label goes to subscript. Is this an obvious glitch?
Which version of Excel do you use? I have all of them on my laptop.
Could you post a workbook that shows the value in cell N21 and a chart so I can try to replicate your problem?
Brad
Could you post a workbook that shows the value in cell N21 and a chart so I can try to replicate your problem?
Brad
ASKER
I just did some Google searching and found a suggestion that it may be a bug in Excel 2007 SP2, which I am running. See http://www.eggheadcafe.com/software/aspnet/35200534/format-only-part-of-a-datalabel-in-excel-2007powerpoint-2007-char.aspx
I might just have to live with it for now.
I might just have to live with it for now.
ASKER
See attached. This shows the same behaviour as my main model. Diagnosing-label-formatting.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks - that's a s far as we can take it then
At least this might help someone else avoid tearing their hair out
At least this might help someone else avoid tearing their hair out
Open in new window
It is worth noting that I encountered a fatal error when more than one cell in the series had a datalabel and they had different number of characters. In other words, if the numbers were subscripted in P90 and P656 there would be a problem. If the two values were P80 and P89, however, the code would work fine.Brad