SHRINK AND WRAP

[Pam M]

Is there any way to keep text wrap but apply the shrink to fit also? When
you click the wrap, the shrink is no longer an option. Selecting shrink
first leaves it checked but it doesn't shrink, it just wraps.

 

[Dave Peterson]

Not that I see.

Maybe you could just try to remember to make the font smaller?

 

[Pam M]

I'm making a form that will be used by many people who don't know how to use
excel. Thanks.

 

[Gord Dibben]

Having both functions in one cell does not make sense so Excel helps you by
not allowing it.

Shrink to fit reduces the font size so's every character will fit the column
width.

What's to wrap?


Gord Dibben MS Excel MVP

 

[Pam M]

Its hard trying to explain yourself to a computer! I have a cell that is 5
wide and 15 high, font size is 8, and cell will hold a job name. Allowing
it to both wrap and shrink to fit would allow the letters to be bigger when
the job name is long, such as Waterford Landing Office Park . Otherwise, the
letters become so tiny to stay on one line that you can't read them.

 

[Gord Dibben]

I grasp the concept of what you want.

Unfortunately Excel is "either/or" in this case.

Would be nice to have a non VBA method of allowing both functions.

Something like this using event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$A$1" And Target.Value <> "" Then
With Target
If Len(.Value) > 10 Then
.Font.Size = 6
.WrapText = True
Else
.Font.Size = 8
End If
End With
End If
stoppit:
Application.EnableEvents = True
End Sub



Gord

 

[Pam M]

Hey, that looks great! I'll give it a try and let you know. In the
meantime, do I put that in the This workbook in the VB editor?

 

[Gord Dibben]

Right-click on the sheet tab and "View Code"

Paste into that sheet module.


Gord