changing font size depending on length of string

[sammendac]

Hi

I need to complete a form. one of the fields has a variable length. I need to have the font size reduced if the length of the string is more than 20.

for a len of less than 20 I can use a 12 Pt Font but for more than 20 a 10 Pt font would fit.

I have tried to do this myself but it always goes bananas

Any help would be appreciated.

 

[RoryA]

When you say a form, do you mean a userform?

 

[sammendac]

No but I am not sure what a userform is.

I take data from our ERP system and produce certificates, such as Certificate of conformity or of Origin. It is typed into a Add data worksheet and then the data is taken to the Certificate sheet. All the fields except the one is question are the same size but the Customer Order No field if of a variable length. If it is over 20 characters it is larger then the cell and doesn't print the full number hence I wish to make it a smaller font to fit.

I tried an If statement on the LEN

similar to

if LEN(range("a32) > 20
large font
else
small font
End if

it's the large/small font I don't seem to be able to get to work.

 

[Fluff]

How about

Sub FontSize()
   Range("A32").Font.size = IIf(Len(Range("A32")) > 20, 10, 12)
End Sub

 

[RoryA]

If it's just a cell on a worksheet, you could set the Shrink to Fit option on the Alignment tab of the Format Cells dialog.

 

[sammendac]

Hi Fluff

That's great and so simple.

Thanks

 

[Fluff]

Glad we could help & thanks for the feedback