two charting problems with excel 2007

[Brian Murphy]

Two forms I use for modifying chart formatting that work fine in excel
2003 do not work in excel 2007.

The first is to copy chart formats from one chart to another. This
snippet of code is in an addin, and copies the formatting of a chart
in the addin to a chart in the user's workbook.
ThisWorkbook.Sheets("API
Level1").DrawingObjects(1).Chart.ChartArea.Copy
ActiveSheet.ChartObjects(1).Activate
With ActiveChart
.ChartArea.Select
.Paste Type:=xlFormats

In excel 2003 this simply paste's the formats. But in excel 2007 it
creates new series, and this messes everything up for me. Is there
some trick to pasting just the formats like in excel 2003?

The second problem is editing the text of a data label. I change the
formatting of individual characters with the following code:
With .Points(1).DataLabel
.Text = "QA"
With .Characters(start:=2, Length:=1).Font
.Subscript = True
End With
End With

The above makes the A a subscript in excel 2003, but in excel 2007 it
makes the entire label subscript, that is, both letters become
subscript. I tried it with and without the named arguments. Is there
a way to get this to work in excel 2007?

Thanks,

Brian Murphy
Austin, Texas

 

[Peter T]

Still here then!

.Paste Type:=xlFormats

That works OK for me in 2007, maybe there's something different about your
chart. You could also try -

ActiveSheet.PasteSpecial Format:=2

but only after ensuring the chart is active of course

If even that fails look into these (which is what I do)

AddChartAutoFormat arg's
ApplyCustomType xlUserDefined Typename
DeleteChartAutoFormat arg


In 2007 DataLabels won't accept mixed formats, workaround is to use
textboxes (don't blame me!)

Regards,
Peter T

 

[Brian Murphy]

Thank you very much, Peter T.

The PasteSpecial method indeed seems to have solved my problem with
copying chart formats. Hurray!

About character formatting of text in a data label. My addin and the
chart that's in it were created with excel 2003, and the data label
uses a mix of Symbol and Arial fonts and one character is a
subscript. It does display this way in both 2003 and 2007 versions.

In Excel 2007 (12.0 6425) when I manually edit the text in a data
label, excel does let me change the font of individual characters.
But it doesn't seem to allow me to make one be a subscript. However,
I can get a subscript by copying and pasting text from another data
label that does have a subscript.

So in a round about way I can get what I want with manual operations,
but not with VBA statements. I can't even apply formatting to
individual characters in VBA, even though it can be done manually. So
I think I'll give up trying. Darn.

Maybe the chief software architects at Microsoft need help managing
their software.

Brian