Adjustable Font Size  
Author Message
ramadu





PostPosted: Wed Oct 17 05:11:13 PDT 2007 Top

Excel Programming >> Adjustable Font Size

I'm using Excel 2003 & 2007, on WinXP2.
I have a large cell (a merged cell 7 columns wide by 10 rows high).
This cell can contain a lot of wrapped text.
What I need is some way to check if the string can't all be displayed at
the current font size, and what size to dynamically reduce the font size
to, to make it fit.

Basically, I need a function that will check the text in a merged cell,
and report the font size needed to make all the text visible. Is that
possible?

Thanks,
Darrem

Excel153  
 
 
Peter





PostPosted: Wed Oct 17 05:11:13 PDT 2007 Top

Excel Programming >> Adjustable Font Size Hi Darren,

I've made a start but best I can say about this (below) is it's work in
progress, in particular not reliable with very long strings over about 1500.
But see how you get on.

- copy the merged area to a temp cell in a totally empty column in say a
hidden sheet
- unmerge the temp cell
- set tmp cell's width to same as merged area's width (see colWd)
- autofit the temp cell's row height
- compare row heights, if temp row height is larger reduce the font size and
autofit until it's same height or less, but bail out if font size reduces to
say 3
- report font size


Sub Setup()
Dim s1$, s2$
s1 = "Some text of unknown width. "
Do While Len(s2) < 1000
s2 = s2 & s1
Loop
s2 = s2 & " END"
With Worksheets("Sheet1").Range("A1")
.WrapText = True
.Resize(10, 7).MergeCells = True
.Value = s2
End With

End Sub

Sub test()
Dim colWd As Single
Dim FntSize As Single
Dim rCheck As Range, rTmp As Range, rCol As Range
Dim ws As Worksheet, wsTmp As Worksheet

Set ws = Worksheets("Sheet1")
Set wsTmp = Worksheets("Sheet2") ' say a hidden sheet

Set rCheck = ws.Range("A1").MergeArea
Set rTmp = wsTmp.Range("A1")

rTmp.Columns.ClearContents
rCheck.Copy rTmp
For Each rCol In rCheck.Columns
colWd = colWd + rCol.ColumnWidth
Next

rTmp.MergeCells = False
rTmp.ColumnWidth = colWd
rTmp.Rows(1).EntireRow.AutoFit
FntSize = rTmp.Font.Size

Do
If rTmp.Height > rCheck.Height Then
FntSize = rTmp.Font.Size
FntSize = FntSize - 0.75
rTmp.Font.Size = FntSize
rTmp.Columns(1).EntireRow.AutoFit
Else
Exit Do '
End If

Loop Until FntSize < 3

Debug.Print "Font size " & FntSize

' rTmp.ClearContents

End Sub

I first tried fixing the row height to same as merged area's height and
autfitt'ing the width, but that seems less reliable than fix width then
autofit height until OK.

Regards,
Peter T




> I'm using Excel 2003 & 2007, on WinXP2.
> I have a large cell (a merged cell 7 columns wide by 10 rows high).
> This cell can contain a lot of wrapped text.
> What I need is some way to check if the string can't all be displayed at
> the current font size, and what size to dynamically reduce the font size
> to, to make it fit.
>
> Basically, I need a function that will check the text in a merged cell,
> and report the font size needed to make all the text visible. Is that
> possible?
>
> Thanks,
> Darrem


 
 
Darren





PostPosted: Wed Oct 17 06:47:35 PDT 2007 Top

Excel Programming >> Adjustable Font Size Fantastic!
The cells rarely contain more than 500 characters, but can be up to 1000
or so, so I shouldn't hit that problem limit.

Can I ask: why does the font size reduce in 0.75 increments? I thought
I'd read font size was in 0.5 increments.

Thanks,
Darren

> Hi Darren,
>
> I've made a start but best I can say about this (below) is it's work in
> progress, in particular not reliable with very long strings over about 1500.
> But see how you get on.
>
> - copy the merged area to a temp cell in a totally empty column in say a
> hidden sheet
> - unmerge the temp cell
> - set tmp cell's width to same as merged area's width (see colWd)
> - autofit the temp cell's row height
> - compare row heights, if temp row height is larger reduce the font size and
> autofit until it's same height or less, but bail out if font size reduces to
> say 3
> - report font size
>
>
> Sub Setup()
> Dim s1$, s2$
> s1 = "Some text of unknown width. "
> Do While Len(s2) < 1000
> s2 = s2 & s1
> Loop
> s2 = s2 & " END"
> With Worksheets("Sheet1").Range("A1")
> .WrapText = True
> .Resize(10, 7).MergeCells = True
> .Value = s2
> End With
>
> End Sub
>
> Sub test()
> Dim colWd As Single
> Dim FntSize As Single
> Dim rCheck As Range, rTmp As Range, rCol As Range
> Dim ws As Worksheet, wsTmp As Worksheet
>
> Set ws = Worksheets("Sheet1")
> Set wsTmp = Worksheets("Sheet2") ' say a hidden sheet
>
> Set rCheck = ws.Range("A1").MergeArea
> Set rTmp = wsTmp.Range("A1")
>
> rTmp.Columns.ClearContents
> rCheck.Copy rTmp
> For Each rCol In rCheck.Columns
> colWd = colWd + rCol.ColumnWidth
> Next
>
> rTmp.MergeCells = False
> rTmp.ColumnWidth = colWd
> rTmp.Rows(1).EntireRow.AutoFit
> FntSize = rTmp.Font.Size
>
> Do
> If rTmp.Height > rCheck.Height Then
> FntSize = rTmp.Font.Size
> FntSize = FntSize - 0.75
> rTmp.Font.Size = FntSize
> rTmp.Columns(1).EntireRow.AutoFit
> Else
> Exit Do '
> End If
>
> Loop Until FntSize < 3
>
> Debug.Print "Font size " & FntSize
>
> ' rTmp.ClearContents
>
> End Sub
>
> I first tried fixing the row height to same as merged area's height and
> autfitt'ing the width, but that seems less reliable than fix width then
> autofit height until OK.
>
> Regards,
> Peter T
>
>


>> I'm using Excel 2003 & 2007, on WinXP2.
>> I have a large cell (a merged cell 7 columns wide by 10 rows high).
>> This cell can contain a lot of wrapped text.
>> What I need is some way to check if the string can't all be displayed at
>> the current font size, and what size to dynamically reduce the font size
>> to, to make it fit.
>>
>> Basically, I need a function that will check the text in a merged cell,
>> and report the font size needed to make all the text visible. Is that
>> possible?
>>
>> Thanks,
>> Darrem
>
>
 
 
Peter





PostPosted: Wed Oct 17 07:42:31 PDT 2007 Top

Excel Programming >> Adjustable Font Size Well that's a turn up !

Have you tested to a string length where it starts to become unreliable, if
so what
( in Setup(), Do While Len(s2) < 1000 ' increase )

> Can I ask: why does the font size reduce in 0.75 increments?

In my system I've always noticed Font size increases in 0.75 increments. If
say the Font size shows '10' on the toolbar it cell.Font.Size returns 9.75.
Actually quite a lot of other points type dimensions also appear to
increment in steps of 0.75. This might be related to my 'typical screen'
res, not sure.

Regards,
Peter T




> Fantastic!
> The cells rarely contain more than 500 characters, but can be up to 1000
> or so, so I shouldn't hit that problem limit.
>
> Can I ask: why does the font size reduce in 0.75 increments? I thought
> I'd read font size was in 0.5 increments.
>
> Thanks,
> Darren

> > Hi Darren,
> >
> > I've made a start but best I can say about this (below) is it's work in
> > progress, in particular not reliable with very long strings over about
1500.
> > But see how you get on.
> >
> > - copy the merged area to a temp cell in a totally empty column in say a
> > hidden sheet
> > - unmerge the temp cell
> > - set tmp cell's width to same as merged area's width (see colWd)
> > - autofit the temp cell's row height
> > - compare row heights, if temp row height is larger reduce the font size
and
> > autofit until it's same height or less, but bail out if font size
reduces to
> > say 3
> > - report font size
> >
> >
> > Sub Setup()
> > Dim s1$, s2$
> > s1 = "Some text of unknown width. "
> > Do While Len(s2) < 1000
> > s2 = s2 & s1
> > Loop
> > s2 = s2 & " END"
> > With Worksheets("Sheet1").Range("A1")
> > .WrapText = True
> > .Resize(10, 7).MergeCells = True
> > .Value = s2
> > End With
> >
> > End Sub
> >
> > Sub test()
> > Dim colWd As Single
> > Dim FntSize As Single
> > Dim rCheck As Range, rTmp As Range, rCol As Range
> > Dim ws As Worksheet, wsTmp As Worksheet
> >
> > Set ws = Worksheets("Sheet1")
> > Set wsTmp = Worksheets("Sheet2") ' say a hidden sheet
> >
> > Set rCheck = ws.Range("A1").MergeArea
> > Set rTmp = wsTmp.Range("A1")
> >
> > rTmp.Columns.ClearContents
> > rCheck.Copy rTmp
> > For Each rCol In rCheck.Columns
> > colWd = colWd + rCol.ColumnWidth
> > Next
> >
> > rTmp.MergeCells = False
> > rTmp.ColumnWidth = colWd
> > rTmp.Rows(1).EntireRow.AutoFit
> > FntSize = rTmp.Font.Size
> >
> > Do
> > If rTmp.Height > rCheck.Height Then
> > FntSize = rTmp.Font.Size
> > FntSize = FntSize - 0.75
> > rTmp.Font.Size = FntSize
> > rTmp.Columns(1).EntireRow.AutoFit
> > Else
> > Exit Do '
> > End If
> >
> > Loop Until FntSize < 3
> >
> > Debug.Print "Font size " & FntSize
> >
> > ' rTmp.ClearContents
> >
> > End Sub
> >
> > I first tried fixing the row height to same as merged area's height and
> > autfitt'ing the width, but that seems less reliable than fix width then
> > autofit height until OK.
> >
> > Regards,
> > Peter T
> >
> >


> >> I'm using Excel 2003 & 2007, on WinXP2.
> >> I have a large cell (a merged cell 7 columns wide by 10 rows high).
> >> This cell can contain a lot of wrapped text.
> >> What I need is some way to check if the string can't all be displayed
at
> >> the current font size, and what size to dynamically reduce the font
size
> >> to, to make it fit.
> >>
> >> Basically, I need a function that will check the text in a merged cell,
> >> and report the font size needed to make all the text visible. Is that
> >> possible?
> >>
> >> Thanks,
> >> Darrem
> >
> >


 
 
Peter





PostPosted: Wed Oct 17 07:59:39 PDT 2007 Top

Excel Programming >> Adjustable Font Size I have just returned a Font Size of 7.75, not sure now it's such a good idea
to increment 0.75.
Might be better to change to steps of 0.25. In the Do loop change -

FntSize = FntSize - 0.75
to
FntSize = FntSize - 0.25

Peter T



> Well that's a turn up !
>
> Have you tested to a string length where it starts to become unreliable,
if
> so what
> ( in Setup(), Do While Len(s2) < 1000 ' increase )
>
> > Can I ask: why does the font size reduce in 0.75 increments?
>
> In my system I've always noticed Font size increases in 0.75 increments.
If
> say the Font size shows '10' on the toolbar it cell.Font.Size returns
9.75.
> Actually quite a lot of other points type dimensions also appear to
> increment in steps of 0.75. This might be related to my 'typical screen'
> res, not sure.
>
> Regards,
> Peter T
>
>


> > Fantastic!
> > The cells rarely contain more than 500 characters, but can be up to 1000
> > or so, so I shouldn't hit that problem limit.
> >
> > Can I ask: why does the font size reduce in 0.75 increments? I thought
> > I'd read font size was in 0.5 increments.
> >
> > Thanks,
> > Darren

> > > Hi Darren,
> > >
> > > I've made a start but best I can say about this (below) is it's work
in
> > > progress, in particular not reliable with very long strings over about
> 1500.
> > > But see how you get on.
> > >
> > > - copy the merged area to a temp cell in a totally empty column in say
a
> > > hidden sheet
> > > - unmerge the temp cell
> > > - set tmp cell's width to same as merged area's width (see colWd)
> > > - autofit the temp cell's row height
> > > - compare row heights, if temp row height is larger reduce the font
size
> and
> > > autofit until it's same height or less, but bail out if font size
> reduces to
> > > say 3
> > > - report font size
> > >
> > >
> > > Sub Setup()
> > > Dim s1$, s2$
> > > s1 = "Some text of unknown width. "
> > > Do While Len(s2) < 1000
> > > s2 = s2 & s1
> > > Loop
> > > s2 = s2 & " END"
> > > With Worksheets("Sheet1").Range("A1")
> > > .WrapText = True
> > > .Resize(10, 7).MergeCells = True
> > > .Value = s2
> > > End With
> > >
> > > End Sub
> > >
> > > Sub test()
> > > Dim colWd As Single
> > > Dim FntSize As Single
> > > Dim rCheck As Range, rTmp As Range, rCol As Range
> > > Dim ws As Worksheet, wsTmp As Worksheet
> > >
> > > Set ws = Worksheets("Sheet1")
> > > Set wsTmp = Worksheets("Sheet2") ' say a hidden sheet
> > >
> > > Set rCheck = ws.Range("A1").MergeArea
> > > Set rTmp = wsTmp.Range("A1")
> > >
> > > rTmp.Columns.ClearContents
> > > rCheck.Copy rTmp
> > > For Each rCol In rCheck.Columns
> > > colWd = colWd + rCol.ColumnWidth
> > > Next
> > >
> > > rTmp.MergeCells = False
> > > rTmp.ColumnWidth = colWd
> > > rTmp.Rows(1).EntireRow.AutoFit
> > > FntSize = rTmp.Font.Size
> > >
> > > Do
> > > If rTmp.Height > rCheck.Height Then
> > > FntSize = rTmp.Font.Size
> > > FntSize = FntSize - 0.75
> > > rTmp.Font.Size = FntSize
> > > rTmp.Columns(1).EntireRow.AutoFit
> > > Else
> > > Exit Do '
> > > End If
> > >
> > > Loop Until FntSize < 3
> > >
> > > Debug.Print "Font size " & FntSize
> > >
> > > ' rTmp.ClearContents
> > >
> > > End Sub
> > >
> > > I first tried fixing the row height to same as merged area's height
and
> > > autfitt'ing the width, but that seems less reliable than fix width
then
> > > autofit height until OK.
> > >
> > > Regards,
> > > Peter T
> > >
> > >


> > >> I'm using Excel 2003 & 2007, on WinXP2.
> > >> I have a large cell (a merged cell 7 columns wide by 10 rows high).
> > >> This cell can contain a lot of wrapped text.
> > >> What I need is some way to check if the string can't all be displayed
> at
> > >> the current font size, and what size to dynamically reduce the font
> size
> > >> to, to make it fit.
> > >>
> > >> Basically, I need a function that will check the text in a merged
cell,
> > >> and report the font size needed to make all the text visible. Is that
> > >> possible?
> > >>
> > >> Thanks,
> > >> Darrem
> > >
> > >
>
>


 
 
Peter





PostPosted: Wed Oct 17 08:28:10 PDT 2007 Top

Excel Programming >> Adjustable Font Size Also - delete the indicated line in the Do Loop

FntSize = rTmp.Font.Size ' KEEP this
Do
If rTmp.Height > rCheck.Height Then
<<< FntSize = rTmp.Font.Size '''' DELETE this,
FntSize = FntSize - 0.25 ' changed from 0.75
rTmp.Font.Size = FntSize
rTmp.Columns(1).EntireRow.AutoFit
Else
Exit Do '
End If
Loop Until FntSize < 3

Peter T


> I have just returned a Font Size of 7.75, not sure now it's such a good
idea
> to increment 0.75.
> Might be better to change to steps of 0.25. In the Do loop change -
>
> FntSize = FntSize - 0.75
> to
> FntSize = FntSize - 0.25
>
> Peter T
>


 
 
Darren





PostPosted: Wed Oct 17 09:53:04 PDT 2007 Top

Excel Programming >> Adjustable Font Size Thanks for the update. I had switched to .25 on my own, and I'll delete
that line now, too.

Darren


> Also - delete the indicated line in the Do Loop
>
> FntSize = rTmp.Font.Size ' KEEP this
> Do
> If rTmp.Height > rCheck.Height Then
> <<< FntSize = rTmp.Font.Size '''' DELETE this,
> FntSize = FntSize - 0.25 ' changed from 0.75
> rTmp.Font.Size = FntSize
> rTmp.Columns(1).EntireRow.AutoFit
> Else
> Exit Do '
> End If
> Loop Until FntSize < 3
>
> Peter T
>
>
>> I have just returned a Font Size of 7.75, not sure now it's such a good
> idea
>> to increment 0.75.
>> Might be better to change to steps of 0.25. In the Do loop change -
>>
>> FntSize = FntSize - 0.75
>> to
>> FntSize = FntSize - 0.25
>>
>> Peter T
>>
>
>