Adjusting height of text cells

[Walter Briscoe]

I write up to about 1000 bytes of text to a cell. Wrap is set.
Sometimes, the cell is taller than it should be by a line of text.
Up to now, most lines have been 17 pixels high and I had a simple macro
to adjust such a cell. I have recently enhanced that macro to deal with
an arbitrary line height. I do not like my code - particularly my
inability to copy a font object with a simple mechanism.
In the code below,
set ActiveCell.Characters(Start:=1, Length:=1).Font = f
gets Run-time error '438' Object doesn't support this property or method
I find I have to copy elements explicitly.
I don't understand why.
debug.print ActiveCell.Characters(Start:=1, Length:=1).Font.Name
works at that point.

I would appreciate helpful advice. Thanks.

Option Explicit

Private Function LineHeight() As Double
' Get pixel height of text in the 2nd cell in the Excel active row.
Dim cell As Range
Dim f As Font
Dim I As Long

' Hide creation and deletion of a temporary row.
Application.ScreenUpdating = False
Set cell = Cells(ActiveCell.Row, 2)
Set f = cell.Characters(Start:=1, Length:=1).Font
For I = 2 To Len(cell.Text)
If f.Size < cell.Characters(Start:=I, Length:=1).Font.Size Then
Set f = cell.Characters(Start:=I, Length:=1).Font
End If
Next I
cell.EntireRow.Insert
ActiveCell = "X"
' set ActiveCell.Characters(Start:=1, Length:=1).Font = f
' gets Run-time error '438'
' Object doesn't support this property or method

With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = f.Name
.FontStyle = f.FontStyle
.Size = f.Size
.Strikethrough = f.Strikethrough
.Superscript = f.Superscript
.Subscript = f.Subscript
.OutlineFont = f.OutlineFont
.Shadow = f.Shadow
.Underline = f.Underline
.ColorIndex = f.ColorIndex
End With
LineHeight = ActiveCell.RowHeight
ActiveCell.EntireRow.Delete Shift:=xlUp
Application.ScreenUpdating = True
End Function

Sub squeezeRow()
'
' squeezeRow Macro
' Macro recorded 10/06/2012 by IBM
'
' Keyboard Shortcut: Ctrl+s
'
' Decrement the number of lines occupied by the active row.
'
' Logic assumes each line is 12.75 points (equivalent to 17 pixels)
' 07/10/2013 - remove that assumption
'
Dim InitialHeight As Double ' height of active row in points
' Const Oneline As Double = 12.75 ' points in single text line row
Dim Oneline As Double
Dim lines As Long
Dim Pixels As Long

Oneline = LineHeight
InitialHeight = ActiveCell.RowHeight
Pixels = InitialHeight * 4
Debug.Assert Pixels Mod 3 = 0
Pixels = Pixels / 3
lines = InitialHeight / Oneline
Debug.Assert InitialHeight = lines * Oneline
If InitialHeight < Oneline * 2 Then Exit Sub
ActiveCell.RowHeight = (lines - 1) * Oneline
End Sub

 

[GS]

I have no answer for your issue, just some food for thought...

There's so many variables that determine a row's height that it's not
even funny! For starters, the font dictates #chars based on whether
it's variable width or fixed width. In addition, use of uppercase chars
affects height, as do bold, italic, and size of single chars (if
different than default size for the cell).

I do a lot of what most people would use Word for in Excel, authoring
manuals of various sorts that also include numerous images. I find that
setting RowHeight to 'n' points per line of text works best, 'n' being
determined by default font size and/or line spacing.

Not saying you can't figure out a formula for a familiar/favorite font
that works with code. I gave up on this and conceeded to setting it
manually/visually based on my formula of n/line plus any adjustment for
added RTF to individual words/chars.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Walter Briscoe]

I have no answer for your issue, just some food for thought...

There's so many variables that determine a row's height that it's not
even funny! For starters, the font dictates #chars based on whether
it's variable width or fixed width. In addition, use of uppercase chars
affects height, as do bold, italic, and size of single chars (if
different than default size for the cell).

I do a lot of what most people would use Word for in Excel, authoring
manuals of various sorts that also include numerous images. I find that
setting RowHeight to 'n' points per line of text works best, 'n' being
determined by default font size and/or line spacing.

Not saying you can't figure out a formula for a familiar/favorite font
that works with code. I gave up on this and conceeded to setting it
manually/visually based on my formula of n/line plus any adjustment for
added RTF to individual words/chars.

Thanks Garry,
That is depressing. Most of my text is Tahoma, 10 point. A few
characters are emboldened and some cells are italicised. Some text is
highlit by using 12 point. That alters pixels per line, which seems to
be a constant, for each cell, whose value is determined by the tallest
character.
I find that what appears on screen is different from what appears on
paper. e.g. a cell may appear on 2 lines on the screen, but on 1 line
followed by a blank line on paper. I sometimes hack to truncate the
rendering of the text on screen, so it appears sensibly on paper. I have
asked questions on such matters without getting anything useful. As you
say, it's not even funny.
I do things manually until that becomes boring and then code something.
My original Sub squeezeRow was probably more than 90% effective. The
current one may be 100%, but is probably only in the high nineties.
The operation is semi-automatic. (Ctrl+s is a keyboard shortcut.) I
should be able to see failures.

 

[GS]

I find that what appears on screen is different from what appears on

paper. e.g. a cell may appear on 2 lines on the screen, but on 1 line
followed by a blank line on paper. I sometimes hack to truncate the
rendering of the text on screen, so it appears sensibly on paper. I
have
asked questions on such matters without getting anything useful. As
you
say, it's not even funny

I resolved to setting the col width to match my page margins so what
I see on the sheet is what I see in PrintPreview. This is usually what
prints as well, but there's almost always some (minor) line spacing
issues on the printout.

I prefer to use Arial font because I find it's much crisper, clearer,
and easier-to-read than Tahoma, generally speaking. I hate that Tahoma
is the default for userforms and so I always make a point to set Arial
right away so all the controls inherit Arial.

Ultimately, the client dictates font used because it reflects
branding. Not sure I agree with that since readability has higher
priority for me. What scares me that given the monumental amount of
info floating around about eyestrain and worker fatigue, one would
think people would smarten up about their reckless use of uppercase and
garrish colors!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion