Size Text Based on Shape Width

[Bandolier]

Hi All,

  I have been reading many (many, many) articles that deal with resizing text based on a shapes dimensions.

  What I am trying to get to is resizing text based on the Width of the Shape AND the Length of the Text itself.

  The formula being something like: Size = the Maximum pt size (between 4 and 12) that will fit the text, on a single line, in the width of the shape.

  At this point I've found I've re-written the same formulas several times over and none are quite doing what I am picturing in my head.   

Thanks in advance!

[Visio Guy]

Hi Bandolier,

I'm pretty sure this involves two formulas: TEXTWIDTH and SETF.

You can see how wide the text is with a formula like this:

User.txtw = TEXTWIDTH(TheText) - LeftMargin - RightMargin

SETF gets a bit more complicated, because you can actually stuff values into other cells.

Unless I've missed an easier solution, to do what you want to do requires some looping. The ShapeSheet can handle some looping, but it wasn't really designed for it.

In words, you might do something like this:

- If the calculated text width is smaller than the shape width, use SETF to bump up the font size by 0.5pt.
- If the calculated text width is bigger than the shape width, use SETF to bump the font size down by 0.5pt.

The SETF will likely happen a few times until the font size is just right. It will be fast, so you won't see the intermediate steps.

I've tried doing this before, and it doesn't always work the way you wish it would, so something is happening in the "in between recalcs" that is hard to trace.

[Bandolier]

Thanks much for the reply!

I'll be trying out the SETF method today/tomorrow so hopefully I will get some (good?) results.

Much appreciated!

[Visio Guy]

Good luck, Bandolier!

Again, I think it should be possible to create. I've personally never gotten it to a form that I felt was good enough for a Visio Guy article. But there are only so many hours in the day!