Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sizing Autoshapes based on text length
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sizing Autoshapes based on text length
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sizing Autoshapes based on text length
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. "Nick Hebb" wrote in message oups.com... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sizing Autoshapes based on text length
Typo
Autofit the column and return it's height. should read of course Autofit the row (entirerow) and return it's height. Peter T "Peter T" <peter_t@discussions wrote in message ... 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. "Nick Hebb" wrote in message oups.com... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sizing Autoshapes based on text length
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sizing Autoshapes based on text length
OK I follow your objective. Doable but rather you than me!
Regards, Peter T "Nick Hebb" wrote in message ups.com... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing a column based on the length of text in a cell in anothercolumn | Excel Worksheet Functions | |||
How to remove text edit from Autoshapes | Excel Discussion (Misc queries) | |||
How can I export text from excel autoshapes to a text file? | Excel Discussion (Misc queries) | |||
How do I "conditionally" change font size based on text length? | Excel Programming | |||
How do you find and replace text in autoshapes or comments | Excel Discussion (Misc queries) |