Issue with Shape(text box)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Anand Nichkaode - 27 Mar 2008 07:14 GMT

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.

Reply to this Message

Peter T - 27 Mar 2008 11:02 GMT

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

> 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
[quoted text clipped - 8 lines]
>
> Thanks in advance.

Reply to this Message

Anand Nichkaode - 27 Mar 2008 12:49 GMT

That was the last resort I had in my mind.

Can you please have a loot at this too.
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.excel.char
ting&tid=3849f722-4381-4c9f-a21d-ae3b74a3b6b2&lang=en&cr=US&p=1

Thx 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 -
[quoted text clipped - 21 lines]
> >
> > Thanks in advance.

Reply to this Message

Peter T - 27 Mar 2008 13:58 GMT

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

> That was the last resort I had in my mind.
>
[quoted text clipped - 29 lines]
>> >
>> > Thanks in advance.

Reply to this Message