Need text width to get the desired text height

[michelleh]

I need to set the size of the text block for my shape.

For most apps, the text width is fixed, and I can use the TextHeight(TheText, TxtWidth) shapesheet function to get the height needed.  For this app, though, I need to limit the text height to a maximum of 5 lines, using the minimum width.

Since there is no corresponding shapesheet function like TextWidth(TheText, TxtHeight), I have some complex formulas that count the number of words and apply a constant to set the TxtWidth to a value that forces the text wrapping to best fill the 5 lines.  It's close, but not perfect, with the width sometimes too small to fit the longest word.  Any ideas on a better way to approach this?

Michelle

[Paul Herber]

There is a TEXTWIDTH(TheText) function.

[michelleh]

TEXTWIDTH does not give a limit to the height, though, so this does not work for my case.  As I said, I need to limit the height to 5 lines of text.

I finally wrote some C# code and did a rough calculation, then gave the user a handle to resize the width manually if the word wrapping doesn't come out right.  If there was a good shapesheet solution I'd like to remove this kludge.

[vojo]

min(6mm, textheight)

[vojo]

can run a conversion of font  number to mm.    conversion has been used to scale font as shape grows/shrinks.
numerous posts for that.    use min to ensure proper height up to a certain value, then capped.

Of course, regardless of the size of the box, the font can be much larger or smaller than the box....so not sure its all that critical

[Hey Ken]

Folks:

    Sorry for resurrecting an eight-year-old thread, but I'm looking to do the same thing Michelle was trying to do, and she summed it up nicely: I need "a corresponding shapesheet function like TextWidth(TheText, TxtHeight)", but "TEXTWIDTH does not give a limit to the height".

    Vojo's suggestion of using MIN only does half the job: it does limit the height, but does not expand the width to keep all the text inside the text box.  If I follow his advice, I then need to adjust the width manually--and you know how much I hate to have extra keystrokes, let alone going through all the effort of lifting my hand off the keyboard to use the mouse.

    The only solution I can envision is to use VBA to iterate through wider and wider widths until the height shrinks to reach the size I need.  See code below.  What I'd really like is a non-VBA solution, but I'm at a loss.  Any suggestions?

    Thanks in advance,

    - Ken

Code Select Expand



Public Sub WidenWidth()

Dim TheShape As Shape
Dim I As Single

Set TheShape = ActiveWindow.Selection(1)

For I = TheShape.Cells("Width").Result(0) To 10 Step 0.125
    ' User.TextHeight contains "=TEXTHEIGHT(TheText,Width)"
    If TheShape.Cells("User.TextHeight").Result(0) > 0.75 Then
        TheShape.Cells("Width").Formula = I
    Else
        Exit For
        End If
    Next I

End Sub



[wapperdude]

Hard to do this in shapesheet without getting circular argument.  Perhaps SETATREF may help?  Something like set min / max limits, for shape height.  If text height exceeds shape height, then extend the width.  Still lingering issue of word split/wrapping I suspect.

Wapperdude