Solved

Formatting within a data series label is lost

Posted on 2011-03-04
10
358 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:sjgrey
  • 5
  • 5
10 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 35041267
You force the data label to have a subscript in very similar fashion to forcing the value in a cell to be subscripted, by referencing the Characters property.
Sub MarkerSubscript()
Dim s As String
With ActiveSheet.ChartObjects(1).Chart
    With .SeriesCollection(1).Points(3)
        s = Range("B2").Value
        .ApplyDataLabels
        .DataLabel.Text = s
        .DataLabel.Characters(2, Len(s) - 1).Font.Subscript = True
    End With
End With
End Sub

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
0
 
LVL 1

Author Comment

by:sjgrey
ID: 35041324
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.
0
 
LVL 81

Expert Comment

by:byundt
ID: 35041408
If I understand you correctly, the syntax would be:
        .DataLabel.Characters(2, 2).Font.Subscript = True

Open in new window

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
0
 
LVL 81

Expert Comment

by:byundt
ID: 35041437
To refer to different series, you would adjust statement 4 to:
    With .SeriesCollection(3).Points(1)          'Refers to third series, first data point 

Open in new window


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

Open in new window


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

Open in new window

Brad

DataLabelSubscriptingQ26865514.xlsm
0
 
LVL 1

Author Comment

by:sjgrey
ID: 35041589
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.

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

Open in new window


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?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 81

Expert Comment

by:byundt
ID: 35041611
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
0
 
LVL 1

Author Comment

by:sjgrey
ID: 35041630
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.
0
 
LVL 1

Author Comment

by:sjgrey
ID: 35041647
See attached.  This shows the same behaviour as my main model. Diagnosing-label-formatting.xlsm
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 35041679
I am replicating your problem with my original workbook in Excel 2007 SP2. Excel 2010 does not exhibit the problem.

Nor does Excel 2010 have the problem with your sample workbook. I therefore conclude that Microsoft fixed the bug in Excel 2010.
0
 
LVL 1

Author Closing Comment

by:sjgrey
ID: 35041703
Thanks - that's a s far as we can take it then

At least this might help someone else avoid tearing their hair out
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now