Fitting long text

[Kurt Remlin]

Hi,

1. Let say I create a worksheet with 3 columns. I will assign "Wrap
Text" attribute to the third column. After I auto-ft the first two
columns, is there a way to programmatically (from VB) set a width of
the third column so it covers the space from its current left border
to the right margin?

2. How exactly does auto-fitting of a multi-line (wrap text) column
works?

TIA

 

[Tom Ogilvy]

This might give you some ideas:

http://groups.google.com/groups?selm=e#[email protected]

 

[Rob van Gelder]

Determining column width as a calculation is difficult - in fact, I don't
know how to do it. It's something to do with font widths in Normal style if
proportional or else use number 0 as a basis. It's crazy.

So as a cheat, the code below keeps widening column 3 until column 4 is no
longer visible.

Sub test()
Dim rng As Range

Set rng = Columns(3)

Application.ScreenUpdating = False
rng.ColumnWidth = 1
Do Until Intersect(ActiveWindow.VisibleRange, rng.Offset(0, 1)) Is
Nothing
rng.ColumnWidth = rng.ColumnWidth + 1
Loop
Application.ScreenUpdating = True
End Sub


As for question 2 - I don't know. It appears to fit so as not to cut a word
in half. Did you really want to know how it works or were you hoping to do
achieve something with AutoFit feature?

 

[Kurt Remlin]

Thank you very much for the response.

As for question 2 - I don't know. It appears to fit so as not to cut a word
in half. Did you really want to know how it works or were you hoping to do
achieve something with AutoFit feature?

All of the above. Since I've tried in a manual mode to auto-fit a
multi-line column and the result was not what I expected, I would like
to know what the original design was as well as a way to circumvent
it.

==============================================================================

 

[Kurt Remlin]

Tom,

Thank you very much for the response.

You base your logic on the ColumnWidth property which is indeed
measured in strange units.

Is it possible to use Width and Left properties of Range combined with
InchesToPoints method of Application instead?

Is this something that has been already considered and rejected?

TIA

=================================================================

 

[Kurt Remlin]

Tom,

Since apparently you can not assign Width property directly, what
about the following work-around?

http://groups.google.com/groups?hl=...-8&safe=off&selm=uuBhgth$BHA.1980@tkmsftngp04
?


============================================================

 

[Tom Ogilvy]

I guess if you know the width to the right margin in points.

 

[TKT-Tang]

I can't help it but asking,

1. How to determine the font width of a character ?

2. Is there an applicable end-of-line control character for wrap-text
as displayed in a worksheet cell ? I do mean to enquire that whether
there are other things besides CHAR(10) and VbCrLf located at the end
of each line of text.

3. I enter long text into a worksheet cell ; the resultant difficulty
incurs the AutoFitting of the Row-Height ; more often than not, the
long text string is not fully displayed automatically ; and there,
mannual intervention is required to drag down the lower border of the
cell in order to render sufficient room for the complete display of
long text string thereof.

Regards.

 

[Kurt Remlin]

Tom,

Yes, I believe I can calculate it.

First I needed the width of the page. Not knowing how to obtain it
directly I had to write the following (quick and dirty) function:

'-----------------------------------------------------
Function PageWidth(xlWB As Excel.Workbook) As Single
' Our printers handle Letter and Legal paper only
With xlWB.ActiveSheet.PageSetup
If .Orientation = xlPortrait Then
PageWidth = 8.5
ElseIf .PaperSize = xlPaperLetter Then
PageWidth = 11
ElseIf .PaperSize = xlPaperLegal Then
PageWidth = 14
Else
PageWidth = 0
End If
End With
End Function
'-----------------------------------------------------

Then it goes like this (1 point = 1/72 inch):

With xlWB.ActiveSheet.PageSetup
sWidth = PageWidth(xlWB) * 72 - .LeftMargin - .RightMargin
End With

And finally:

With xlWS.Columns("F").Cells
If sWidth > .Left Then
.ColumnWidth = .ColumnWidth / .Width * (sWidth - .Left)
End If
End With

This code works for me.


====================================================================

 

[Rob van Gelder]

A long time ago I came across an API which measured the wdth of a sentence
given font information and a string - but I lost it and I haven't been able
to find it since.