CODEWITHSUNDEEP
excelvba
Johan
Johan

Reputation: 76724

Given a fixed cel width, can I change the fontsize so that the text will fit inside the cell?

I have code that will automatically adjust the height of a cell, so that text will fit inside that cell.
However now I want to change the fontsize of the text inside the cell, so that the font will shrink and grow to make the text fit inside the cell, so I don't have to make the cell width a fixed size.

How do I do this in VBA?

I need to know the width of text, given a font size

Option 1.

TextWidth = WidthOfAText(Range("A1").Value)
FontAdjustmentFactor = Range("A1").EntireColumn.ColumnWidth / TextWidth
FontSize = Range("A1").Font.Size * FontAdjustmentFactor
Range("A1").Font.Size = Max(4, Min(10, FontSize))

or I need to know whether the text will fix and do some sort of trial and error routine.

Option 2.

Range("A1").Font.Size = 10
While (Text_does_not_fit AND Range("A1").Font.Size >= 5) 
  Range("A1").Font.Size = Range("A1").Font.Size - 1
Wend

How do I do this? (Preferably using code like in option 1)

Upvotes: 1

Views: 6447

Answers (1)

TheFuzzyGiggler
TheFuzzyGiggler

Reputation: 959

Non programatically just right click on the cell -> Format Cells -> Alignment -> Shrink to Fit

Code wise is:

Sheet1.Range("A1").ShrinkToFit = True

Upvotes: 4

Related Questions