+ Reply to Thread
Results 1 to 6 of 6

Sizing Autoshapes based on text length

  1. #1
    Nick Hebb
    Guest

    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. #2
    Nick Hebb
    Guest

    Re: 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. #3
    Peter T
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #4
    Peter T
    Guest

    Re: 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
    news:#[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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. #5
    Nick Hebb
    Guest

    Re: 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. #6
    Peter T
    Guest

    Re: Sizing Autoshapes based on text length

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

    Regards,
    Peter T

    "Nick Hebb" <[email protected]> wrote in message
    news:[email protected]...
    > > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1