Sizing Autoshapes based on text length

[Nick Hebb]

The automatic sizing feature in Autoshape formatting seems buggy to me.
I'm using Excel XP, and when I auto size, it resizes the shape so that
all the text is on one line.

I would like to keep the width of the shape the same, but adjust the
height based on the length of the text. Anyone got any ideas on how I
can do this in VBA?

Thanks,
Nick

 

[Nick Hebb]

BTW, a good example of what I'm talking about can be seen in Word. When
you doing automatic sizing in Word with the wrap text option selected,
the shape width remains the same but the height adjusts to fit.

 

[Peter T]

Hi Nick,

Couple of ways -

Put text into a cell in an empty row and set column to pre determined width.
Wrap text. Autofit the column and return it's height.

A textbox will autosize to height if the text has line breaks. This would
entail guestimating max characters that would fit into your pre-determined
width, finding the first space to the left of the end and replacing with a
linefeed, not chr(13) or vbCr, but a single vbLf or chr(10).
Also pad one line with characters that will autosize to correct width (to be
removed later). Cater for margins.

Once autosize'd, if you want to replace text without the line feeds, remove
the Autosize setting having got the new dimensions to re-dimension if
necessary.

I find textbox autosize more accurate than sheet column autosize. The latter
approximates according to the default font width and the small cell indent
is slightly version dependant. However the cell method is much simpler,
although there is a max string length limit.

Not sure I've explained all that very well!

Regards,
Peter T

PS Maybe a third way might be to do it in a hidden instance of Word the way
you say - never tried.

 

[Peter T]

Typo

Autofit the column and return it's height.

should read of course

Autofit the row (entirerow) and return it's height.

Peter T

 

[Nick Hebb]

Not sure I've explained all that very well!

Explanations were perfectly clear.

Unfortunately, nothing is really working well for the non-rectangular
autoshapes like flowchart decision shapes (diamonds). It would be a lot
easier if I could get a handle for the textframe object and determine
it's width. But for now I've had to do mockups for each shape based on
the standard font and make adjustments for different fonts and sizes.
It's gotten fairly convoluted.

 

[Peter T]

OK I follow your objective. Doable but rather you than me!

Regards,
Peter T