Automatically change font size

[sparky3883]

Hi all

I was just wondering; If you enter some numbers into a Cell (fo
example 123456) and the Cell was only big enough to show 3 numbers (an
more than 3 it displays ####), is there some coding that you can appl
to your sheet to make the font size go smaller dependant on the amoun
of digits entered?

If there is, is it possible to make it apply to every Cell on th
sheet

Any help would be much appreciated

Cheers in advanc

 

[JE McGimpsey]

via code:

Cells.ShrinkToFit = True

will apply shrink to fit to all cells in the activesheet.

Sheets("Sheet1").Cells.ShrinkToFit = True

will work for Sheet1.

 

[Frank Kabel]

Hi
this is only possible with VBA (using an event macro to to this). e.g.
the following will shrink the font size to 8 points in column A if you
enter more than 3 digits (put this in your worksheet module):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.count > 1 Then Exit Sub
If Application.Intersect(Target, Range("A:A")) Is _
Nothing Then Exit Sub

On Error GoTo ErrorHandler
Application.EnableEvents = False
With Target
If Len(.Value) > 3 "" Then
.font.size = 8
End If
End If
End With
ErrorHandler:
Application.EnableEvents = True
End Sub

 

[Tom Ogilvy]

Couple of typos:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
On Error GoTo ErrorHandler
Application.EnableEvents = False
With Target
If Len(.Value) > 3 Then
.Font.Size = 8
End If
End With
ErrorHandler:
Application.EnableEvents = True
End Sub

 

[Frank Kabel]

Hi Tom
thanks for the correction (shouldn't code in the newsreader window...)
Frank

 

[David McRitchie]

Even though this is the programming group, since the poster
didn't seem to know about "shrink to fit" would also point out that it
is available manually for a selection (or select all cells with Ctrl+A)
under Format, cells, alignment, Shrink to Fit. You get what you
ask -- it can be very small.