Fitting long text
 

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

Fitting long text
 

This might give you some ideas:

http://groups.google.com/groups?selm...GP10.phx. gbl

--
Regards,
Tom Ogilvy

"Kurt Remlin" wrote in message
om...
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

Fitting long text
 

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?


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Kurt Remlin" wrote in message
om...
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

Fitting long text
 

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.

================================================== ============================
"Rob van Gelder" wrote in message ...
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?


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Kurt Remlin" wrote in message
om...
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

Fitting long text
 

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

================================================== ===============
"Tom Ogilvy" wrote in message ...
This might give you some ideas:

http://groups.google.com/groups?selm...GP10.phx. gbl

--
Regards,
Tom Ogilvy

"Kurt Remlin" wrote in message
om...
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

Fitting long text
 

Tom,

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

http://groups.google.com/groups?hl=en&lr=lang_en|lang_ru&ie=UTF-8&oe=UTF-8&safe=off&selm=uuBhgth%24BHA.1980%40tkmsftngp04
?


================================================== ==========
"Tom Ogilvy" wrote in message ...
This might give you some ideas:

http://groups.google.com/groups?selm...GP10.phx. gbl

--
Regards,
Tom Ogilvy

"Kurt Remlin" wrote in message
om...
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

Fitting long text
 

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

--
Regards,
Tom Ogilvy

"Kurt Remlin" wrote in message
m...
Tom,

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


http://groups.google.com/groups?hl=en&lr=lang_en|lang_ru&ie=UTF-8&oe=UTF-8&safe=off&selm=uuBhgth%24BHA.1980%40tkmsftngp04
?


================================================== ==========
"Tom Ogilvy" wrote in message
...
This might give you some ideas:


http://groups.google.com/groups?selm...GP10.phx. gbl

--
Regards,
Tom Ogilvy

"Kurt Remlin" wrote in message
om...
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

Fitting long text
 

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.

Fitting long text
 

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.


================================================== ==================
"Tom Ogilvy" wrote in message ...
I guess if you know the width to the right margin in points.

--
Regards,
Tom Ogilvy

"Kurt Remlin" wrote in message
m...
Tom,

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


http://groups.google.com/groups?hl=en&lr=lang_en|lang_ru&ie=UTF-8&oe=UTF-8&safe=off&selm=uuBhgth%24BHA.1980%40tkmsftngp04
?


================================================== ==========
"Tom Ogilvy" wrote in message
...
This might give you some ideas:


http://groups.google.com/groups?selm...GP10.phx. gbl

--
Regards,
Tom Ogilvy

"Kurt Remlin" wrote in message
om...
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

Fitting long text
 

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.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"TKT-Tang" wrote in message
m...
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.