I have a worksheet that has rows formatted to auto fit and cells formatted to
word wrap, but the cells do not auto adjust to input data. The column is set
to a width of 40. If the input string is over 39 characters long, the cell
stays 1 row high until the character length exceeds 51. I have tried the
attached code from Gord Dibben, but it does not resolve the problem.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

Jack

The width set at 40 does not govern how many characters will show before the
text will wrap or autofit.

The number that appears in the Standard column width box is the average number
of digits 0-9 of the standard font that fit in a cell.

Depending upon the characters that could be more or less.

I tested with the letter "i" and got 93 before wrapping or autofit took place.

With "w" it took only 30.

Formatted as 10 point Arial Font.


Gord Dibben MS Excel MVP

On Tue, 9 Jan 2007 08:46:00 -0800, Jack Greb <Jack
wrote:

I have a worksheet that has rows formatted to auto fit and cells formatted to
word wrap, but the cells do not auto adjust to input data. The column is set
to a width of 40. If the input string is over 39 characters long, the cell
stays 1 row high until the character length exceeds 51. I have tried the
attached code from Gord Dibben, but it does not resolve the problem.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

I mentioned the width vs length only to demonstrate that here were an excess
number of characters required before the wrap takes place. This leaves some
of the characters hidden if they are below the magic threshhold. What I need
is a method to wrap as soon as a character string exceeds what can be seen.

"Gord Dibben" wrote:

Jack

The width set at 40 does not govern how many characters will show before the
text will wrap or autofit.

The number that appears in the Standard column width box is the average number
of digits 0-9 of the standard font that fit in a cell.

Depending upon the characters that could be more or less.

I tested with the letter "i" and got 93 before wrapping or autofit took place.

With "w" it took only 30.

Formatted as 10 point Arial Font.


Gord Dibben MS Excel MVP

On Tue, 9 Jan 2007 08:46:00 -0800, Jack Greb <Jack
wrote:

I have a worksheet that has rows formatted to auto fit and cells formatted to
word wrap, but the cells do not auto adjust to input data. The column is set
to a width of 40. If the input string is over 39 characters long, the cell
stays 1 row high until the character length exceeds 51. I have tried the
attached code from Gord Dibben, but it does not resolve the problem.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

I mentioned the width vs length only to demonstrate that here were an excess
number of characters required before the wrap takes place. This leaves some
of the characters hidden if they are below the magic threshold. What I need
is a method to wrap as soon as a character string exceeds what can be seen.

"Gord Dibben" wrote:

Jack

The width set at 40 does not govern how many characters will show before the
text will wrap or autofit.

The number that appears in the Standard column width box is the average number
of digits 0-9 of the standard font that fit in a cell.

Depending upon the characters that could be more or less.

I tested with the letter "i" and got 93 before wrapping or autofit took place.

With "w" it took only 30.

Formatted as 10 point Arial Font.


Gord Dibben MS Excel MVP

On Tue, 9 Jan 2007 08:46:00 -0800, Jack Greb <Jack
wrote:

I have a worksheet that has rows formatted to auto fit and cells formatted to
word wrap, but the cells do not auto adjust to input data. The column is set
to a width of 40. If the input string is over 39 characters long, the cell
stays 1 row high until the character length exceeds 51. I have tried the
attached code from Gord Dibben, but it does not resolve the problem.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

Jack

I don't have an answer for your problem.

I cannot replicate it. When I enter text in a wrap-text autofit cell the text
wraps and the row re-sizes when it reaches the end of the column width.

Are you dealing with "merged cells"?

They can cause problems.

Maybe another watcher can contribute something.


Gord

On Wed, 10 Jan 2007 15:21:00 -0800, Jack Greb
wrote:



I mentioned the width vs length only to demonstrate that here were an excess
number of characters required before the wrap takes place. This leaves some
of the characters hidden if they are below the magic threshold. What I need
is a method to wrap as soon as a character string exceeds what can be seen.

"Gord Dibben" wrote:

Jack

The width set at 40 does not govern how many characters will show before the
text will wrap or autofit.

The number that appears in the Standard column width box is the average number
of digits 0-9 of the standard font that fit in a cell.

Depending upon the characters that could be more or less.

I tested with the letter "i" and got 93 before wrapping or autofit took place.

With "w" it took only 30.

Formatted as 10 point Arial Font.


Gord Dibben MS Excel MVP

On Tue, 9 Jan 2007 08:46:00 -0800, Jack Greb <Jack
wrote:

I have a worksheet that has rows formatted to auto fit and cells formatted to
word wrap, but the cells do not auto adjust to input data. The column is set
to a width of 40. If the input string is over 39 characters long, the cell
stays 1 row high until the character length exceeds 51. I have tried the
attached code from Gord Dibben, but it does not resolve the problem.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

Gord,
I can send you a copy of the shet if you can provide me with an address.
Perhaps you will be able to spot the problem.

"Gord Dibben" wrote:

Jack

I don't have an answer for your problem.

I cannot replicate it. When I enter text in a wrap-text autofit cell the text
wraps and the row re-sizes when it reaches the end of the column width.

Are you dealing with "merged cells"?

They can cause problems.

Maybe another watcher can contribute something.


Gord

On Wed, 10 Jan 2007 15:21:00 -0800, Jack Greb
wrote:



I mentioned the width vs length only to demonstrate that here were an excess
number of characters required before the wrap takes place. This leaves some
of the characters hidden if they are below the magic threshold. What I need
is a method to wrap as soon as a character string exceeds what can be seen.

"Gord Dibben" wrote:

Jack

The width set at 40 does not govern how many characters will show before the
text will wrap or autofit.

The number that appears in the Standard column width box is the average number
of digits 0-9 of the standard font that fit in a cell.

Depending upon the characters that could be more or less.

I tested with the letter "i" and got 93 before wrapping or autofit took place.

With "w" it took only 30.

Formatted as 10 point Arial Font.


Gord Dibben MS Excel MVP

On Tue, 9 Jan 2007 08:46:00 -0800, Jack Greb <Jack
wrote:

I have a worksheet that has rows formatted to auto fit and cells formatted to
word wrap, but the cells do not auto adjust to input data. The column is set
to a width of 40. If the input string is over 39 characters long, the cell
stays 1 row high until the character length exceeds 51. I have tried the
attached code from Gord Dibben, but it does not resolve the problem.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

Jack sent me the workbook.

Text is not wrapping until well past the right cell boundary.

As Jack points out....should wrap at 40 chars but doesn't wrap until 51 chars
are reached.

Zoom settings are at 60% for the sheet which causes the problem in my
estimation.

By re-setting to 80% or above the text wraps where it should and autofit takes
place.

Anybody have any suggestions other than "increase the zoom".


Gord


On Wed, 10 Jan 2007 17:37:25 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

Jack

I don't have an answer for your problem.

I cannot replicate it. When I enter text in a wrap-text autofit cell the text
wraps and the row re-sizes when it reaches the end of the column width.

Are you dealing with "merged cells"?

They can cause problems.

Maybe another watcher can contribute something.


Gord

On Wed, 10 Jan 2007 15:21:00 -0800, Jack Greb
wrote:



I mentioned the width vs length only to demonstrate that here were an excess
number of characters required before the wrap takes place. This leaves some
of the characters hidden if they are below the magic threshold. What I need
is a method to wrap as soon as a character string exceeds what can be seen.

"Gord Dibben" wrote:

Jack

The width set at 40 does not govern how many characters will show before the
text will wrap or autofit.

The number that appears in the Standard column width box is the average number
of digits 0-9 of the standard font that fit in a cell.

Depending upon the characters that could be more or less.

I tested with the letter "i" and got 93 before wrapping or autofit took place.

With "w" it took only 30.

Formatted as 10 point Arial Font.


Gord Dibben MS Excel MVP

On Tue, 9 Jan 2007 08:46:00 -0800, Jack Greb <Jack
wrote:

I have a worksheet that has rows formatted to auto fit and cells formatted to
word wrap, but the cells do not auto adjust to input data. The column is set
to a width of 40. If the input string is over 39 characters long, the cell
stays 1 row high until the character length exceeds 51. I have tried the
attached code from Gord Dibben, but it does not resolve the problem.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub