Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adjusting height of text cells
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 -- Walter Briscoe |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adjusting height of text cells
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adjusting height of text cells
In message of Tue, 8 Oct 2013 10:56:36 in
microsoft.public.excel.programming, GS writes 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. -- Walter Briscoe |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adjusting height of text cells
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[s] 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[s] 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row height not auto adjusting on wrapped cells (Excel '07) | Excel Discussion (Misc queries) | |||
Adjusting row height while wrapping text | Excel Discussion (Misc queries) | |||
Adjusting row height to accomidate text in a cell | Excel Discussion (Misc queries) | |||
Automatically Adjusting Row Height in Merged Cells | Excel Discussion (Misc queries) | |||
Row height is not adjusting after activating Merge Cells & Wrap Te | Excel Worksheet Functions |