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.

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" wrote in
message ...
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.

That was the last resort I had in my mind.

Can you please have a loot at this too.
http://msdn.microsoft.com/newsgroups...g=en&cr=US&p=1

Thx in advance.


"Peter T" wrote:

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" wrote in
message ...
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.

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

"Anand Nichkaode" wrote in
message ...
That was the last resort I had in my mind.

Can you please have a loot at this too.
http://msdn.microsoft.com/newsgroups...g=en&cr=US&p=1

Thx in advance.


"Peter T" wrote:

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" wrote in
message ...
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.