superscript in vba? 5

[GovNewbi]

Is there a way to have an excel chart's title contain numbers to an exponent? I want the title for the y-axis of my chart to have the units of measure which are cubic meters. Everytime I run my vb code the chart shows (m3). Is there a way to superscript the 3?

 

[combo]

Assuming that you format active chart:

ActiveChart.Axes(xlValue).AxisTitle.Characters(Start:=2, Length:=1).Font.Superscript = True

combo

 

[HughLerwill]

Combo, that is very handy, I notice this works too..

Cells(1, 1).Characters(Start:=2, Length:=4).Font.Subscript = True

A Star for you.

 

[fumei]

I have to agree. Stars to both of you, as I did not know you could action partial portions of cell text like that either.

Gerry

 

[mscallisto]

ditto to what fumei said

sam

 

[GovNewbi]

This dosn't seem to be working... here is the code I have

ActiveChart.Axes(xlValue).AxisTitle.Select
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Volume (106m3)"
ActiveChart.Axes(xlValue).AxisTitle.Characters(Start:=11, Length:=1).Font.Superscript = True
ActiveChart.Axes(xlValue).AxisTitle.Characters(Start:=13, Length:=1).Font.Superscript = True

But the title is ALL superscript now???

 

[SkipVought]

maybe this?

'        ActiveChart.Axes(xlValue).AxisTitle.Select
   With ActiveChart.Axes(xlValue, xlPrimary).AxisTitle
        .Text = "Volume (106m3)"
        i = len(.Text)
        .Characters(Start:=1, Length:=i-1).Font.Superscript = [b]False[/b]
        .Characters(Start:=i, Length:=1).Font.Superscript = [b]True[/b]
   End with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GovNewbi]

nope it still takes the whole thing and makes it superscript????

 

[SkipVought]

Try this INSERTING a Shapes Textbox in the chart...

    Dim i As Integer
   With ActiveChart.Shapes("Textbox 1").OLEFormat.Object
        .Text = "Volume (106m3)"
        i = Len(.Text)
        .Characters(Start:=1, Length:=i - 2).Font.Superscript = False
        .Characters(i - 1, 1).Font.Superscript = True
        .Characters(i, 1).Font.Superscript = False
   End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[HughLerwill]

Did you try;

ActiveChart.Axes(xlValue).AxisTitle.Select
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Volume (106m3)"
ActiveChart.Axes(xlValue).AxisTitle.Characters(Start:=12, Length:=2).Font.Superscript = True

..yet?

 

[SkipVought]

Hugh, that makes the entire AxisTitle superscript.

Strange that it can be done manually but not via code???

That's why I suggested the Textbox approch.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[HughLerwill]

Skip sorry I had'nt tested, just a thought.

 

[SkipVought]

I agree. It SHOULD work. The only caveat is in VBA Help, on the Characters Object...

The Characters method is necessary only when you need to change some of an object’s text without affecting the rest (you cannot use the Characters method to format a portion of the text if the object doesn’t support rich text). To change all the text at the same time, you can usually apply the appropriate method or property directly to the object. The following example formats the contents of cell A5 as italic.

But this does not explain why this can be performed manually but not via code, unless I am missing something.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[HughLerwill]

The thing works on a Cell so how about doing it to the required text in a Cell and then doing something like;

ActiveChart.Axes(xlValue, xlPrimary).AxisTitle = Cell

 

[combo]

Check the formatting of the whole title - are there any effects set to the font? Font settings can be done manually. You could try to record formatting in the macro. The result for me (slightly modified):

ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Volume (106m3)"
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters(Start:=11, Length:=1).Font.Superscript = True
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters(Start:=13, Length:=1).Font.Superscript = True

combo

 

[SkipVought]

combo, this code assigns the entire text of the AxisTitle object as superscript. The Characters method seems to have no discriminating effect.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[HughLerwill]

Ditto here Skip and my last idea of something like;

ActiveChart.Axes(xlValue, xlPrimary).AxisTitle = Cell(m,n)

fails too.

 

[SkipVought]

The only method that has worked for me is a textbox (not forms or control toolbox). I suppose you could assign the caption to the AxisTitle and then 'cover' it with a textbox, assigning it the postion, dimensions, text of the AxisTitle and format THAT substring with superscript.

Oddly enough, in 2005, I cannot seem to macro record ADDing a textbox to the sheet???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

i meant Excel 2007

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

Am I missing something here?

I just tried out the following and it works fine. It changes the 6 and the 3 to superscript and nothing else.

With ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue).AxisTitle
   .Characters.Text = "Volume (106 m3)"
   .Characters(Start:=11, Length:=1).Font.Superscript = True
   .Characters(Start:=14, Length:=1).Font.Superscript = True
End With

Of course, I'm still in the dark ages using office 97, so maybe that has something to do with it.

Tony