Issue with Shape(text box)

[Anand Nichkaode]

I have macro in my workbook (Excel2003) which breaks formula links for text
boxes present in the workbook. The code used for the same is
"shp.DrawingObject.Formula = Empty"

This works perfectly fine in Excle 2003 but, when run in Excel 2007 all the
textboxes whose formula link is broken lose the formatting.

I tried to search for DrawingObject information, but couldn't find anything.

Any help appreciated.

Thanks in advance.

 

[Peter T]

I don't have Excel 2007 but from what you describe it sounds like you'll
need to trap the original formats and re-apply when done. Eg read/write to -

shp.TextFrame.Characters.Font.Bold

I assume you won't have mixed formats to contend with.

Regards,
Peter T

 

[Anand Nichkaode]

That was the last resort I had in my mind.

Can you please have a loot at this too.
http://msdn.microsoft.com/newsgroup...4381-4c9f-a21d-ae3b74a3b6b2&lang=en&cr=US&p=1

Thx in advance.

 

[Peter T]

I'll hold back from replying in your .charting post as your issue seems to
be related to behaviour in Excel 2007, which I don't have. However I'm
pretty sure I've read of similar reports, Jon & Andy might suggest
something. Otherwise it might be a case of upgrading to XL2003, though even
in earlier versions radical data changes can mess up axis & label
formatting.

Maybe temporarily add your chart to the user gallery and reapply after
making changes, then delete the custom chart (which incidentally is what I
do to undo format changes) .

For the textboxes it's not to difficult to trap formats into a 2D array of
tb.index by formats to store (eg bold, italic, size etc) and reapply.
Storing all chart formats is quite a lot of work as the approach may need to
be different depending on the individual chart.

Regards,
Peter T