Auto Resize Text

[austin350s10]

Is there a way using VBA to make the text in a box, consisting of merged cells, auto fit the box?

Example:

The user enters a small amount of text in this large area of merged cells and runs AutoResizeText script. The script should resize the text to lets say a maximum of font size 16.

On the flip side, the user enters a large amount of text into the same area of merged cells but this time the text does not fit into the merged cell area. When the user runs the AutoResizeText script it should resize the text to lets say a minimum of font size 8 .

Does anyone think this can be done?

 

[austin350s10]

Anyone??

 

[andy3816]

It can be done

but its clunky

you will need to calculate first the maximum number of characters in the text string that will fit at whatever is the std font, then code to change the font accordingly based on the number of characters entered

LEN(A1) will return total string length
then

if LEN < whatever then range.font.size=whatever

or something similar