• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

superscript/subscript a portion of data label

S

sifar786

Member
hi,

i am trying to Superscript the a portion of the text in datalabels in a chart placed on a worksheet.

Code: 
Set dl=Cht.SeriesCollection(j).DataLabels
dl(i).Characters(Start:=LStart, Length:=1).Font.Superscript =msoTrue

but it seems to Superscript the entire data label text. Also it seems to work properly in Excel 2003, but doesn't seem to work in Excel 2007/2010.
 
Hi,

I have attached a sample file with my code in it.
What i am trying to do is Superscript the Letters only and not the numbers. Interestingly, the code does superscript each data label correctly, but once it moves to another datalabel in the same series or other series, it seems to superscript some of the earlier data labels incorrectly - i.e. some numbers also get superscripted along with the letters.

What is the cause of such erratic behaviour?

Any help will be most appreciated.
 

Attachments

  • Chart_SuperscriptTesting.xlsm
    23.5 KB · Views: 10
Hi Sifar,

I tried my best to figure this out, but seems to be a bug with Excel. Its the third datalabel onwards which acts weird and change the other datalabels font as well. Its not only through VBA programming but this happens even if manually change the font settings.

Will this help you : Creating textboxes with superscript text to look like a datalabel and grouping them with the chart. (Incase your datalabels are very less to edit on)
 
Hi Sriram ,

I use Excel 2007 , and when I manually superscript each datalabel one after the other , there is no problem.

I think a solution to the VBA problem may be possible , but it needs some trial and error effort ; difficult to say whether it is worthwhile !

Narayan
 
Hi Narayan,

I am using Excel 2010 and it changes the other datalabels when you try to modify the 3rd datalabel onwards. Would you please spend sometime on the coding of the file and see what's causing this. I am not able to figure out whether its an Excel issue or something wrong with the way code is dealing the datalabels.
 
Hi Sriram ,

When I use VBA I find that there is a problem of characters changing to superscript / subscript at random ; it must be an Excel issue , since when I step through the code , everything works as expected for the first datalabel , but by the time the second or third datalabel is done , the first one has changed on its own !

There must be a workaround , but as I said earlier , it will take some time and effort to find it out.

Narayan
 
Thanks Narayan,

Though its not me who raised this issue but Sifar, I am really interested to see how to get this issue down.
 
Hi Sriram ,

I did some more troubleshooting , and what I find is that the erratic behaviour is only when the position of the characters to be sub / super-scripted change from one label to the next within a series.

Suppose the first two or the last two characters of all the datalabels in a series are to be sub / super-scripted ; the code works without any problem , even when there is a difference between series e.g. suppose the first series has the last two characters superscripted , while the second series has the first two characters superscripted ; the code works without any problem.

It is only when the positions change within a series that the problem crops up.

Narayan
 
Could you test this?

Code: 
Sub DoIt()

    Dim Cht As ChartObject
    Dim ser As Series
    Dim I As Long, J As Long, K As Long
    With Me
        For Each Cht In .ChartObjects
            On Error Resume Next
            With Cht.Chart
                For Each ser In .SeriesCollection
                    For K = 1 To ser.Points.Count
                        With ser.Points(K).DataLabel
                            For J = 1 To .Characters.Count
                                .Characters(J, 1).Font.Superscript = Asc(UCase(.Characters(J, 1).Text)) > 64 And Asc(UCase(.Characters(J, 1).Text)) < 91
                            Next J
                        End With
                    Next K
                Next ser
            End With
            On Error GoTo 0
        Next Cht
    End With
        
    Set Cht = Nothing
    Set ser = Nothing

End Sub
 
Sam,

Thanks for this but still no luck as it is changing the character font property for all the datalabels. I am using Excel 2010.
 
Thanks Narayan, I referred the link Sam has posted and it seems the issue is only with Excel 2010. Also, is this because theoretically Superscripts appear at the end of the text and Excel is understanding it to Autocorrect?
 
You must log in or register to reply here.
Back
Top