Autofit and word wrap don't work?
 

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

Autofit and word wrap don't work?
 

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

Autofit and word wrap don't work?
 

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

Autofit and word wrap don't work?
 

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

Autofit and word wrap don't work?
 

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

Autofit and word wrap don't work?
 

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

Autofit and word wrap don't work?
 

Send away.

Change the AT and DOT to appropriate punctuation marks.


Gord

On Wed, 10 Jan 2007 19:31:00 -0800, Jack Greb
wrote:

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

Autofit and word wrap don't work?
 

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

Autofit and word wrap don't work?
 

Correction......

In the workbook I received from Jack the number of chars were 22 and 30

Not that it makes any difference but might as well get the numbers right<g


Gord

On Sat, 13 Jan 2007 20:01:59 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

Autofit and word wrap don't work?
 

This is a guess...

Excel will substitute different fonts when you get less than 80% (IIRC).

Maybe this post by Debra Dalgleish will help....

You can change a registry setting to prevent this from happening (make a
backup copy of the registry first):

1. From the Start button, choose Run
2. Type regedit then click OK
3. Click the + sign to the left of HKEY_CURRENT_USER
4. Open Software, Microsoft, Office, 9.0, Excel, Options
5. Choose EditNewDWORD Value
6. Type the name for the DWORD: FontSub
7. Press Enter to complete the renaming
8. Choose EditModify
9. Type 0 as the value, select Decimal, and click OK
10. Close the Registry Editor

=====
That 9.0 will vary with your version of excel.

Gord Dibben wrote:

Correction......

In the workbook I received from Jack the number of chars were 22 and 30

Not that it makes any difference but might as well get the numbers right<g

Gord

On Sat, 13 Jan 2007 20:01:59 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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



--

Dave Peterson

Autofit and word wrap don't work?
 

It may be when the zoom is less than 100%. (I went back and checked my notes.)

Dave Peterson wrote:

This is a guess...

Excel will substitute different fonts when you get less than 80% (IIRC).

Maybe this post by Debra Dalgleish will help....

You can change a registry setting to prevent this from happening (make a
backup copy of the registry first):

1. From the Start button, choose Run
2. Type regedit then click OK
3. Click the + sign to the left of HKEY_CURRENT_USER
4. Open Software, Microsoft, Office, 9.0, Excel, Options
5. Choose EditNewDWORD Value
6. Type the name for the DWORD: FontSub
7. Press Enter to complete the renaming
8. Choose EditModify
9. Type 0 as the value, select Decimal, and click OK
10. Close the Registry Editor

=====
That 9.0 will vary with your version of excel.

Gord Dibben wrote:

Correction......

In the workbook I received from Jack the number of chars were 22 and 30

Not that it makes any difference but might as well get the numbers right<g

Gord

On Sat, 13 Jan 2007 20:01:59 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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



--

Dave Peterson

--

Dave Peterson

Autofit and word wrap don't work?
 

Thanks Dave

I did as instructed and the wrap text and autofit now work as we desire when
zoom at 60%

Have emailed Jack pointing him to your posting.


Gord

On Sun, 14 Jan 2007 06:45:51 -0600, Dave Peterson
wrote:

This is a guess...

Excel will substitute different fonts when you get less than 80% (IIRC).

Maybe this post by Debra Dalgleish will help....

You can change a registry setting to prevent this from happening (make a
backup copy of the registry first):

1. From the Start button, choose Run
2. Type regedit then click OK
3. Click the + sign to the left of HKEY_CURRENT_USER
4. Open Software, Microsoft, Office, 9.0, Excel, Options
5. Choose EditNewDWORD Value
6. Type the name for the DWORD: FontSub
7. Press Enter to complete the renaming
8. Choose EditModify
9. Type 0 as the value, select Decimal, and click OK
10. Close the Registry Editor

=====
That 9.0 will vary with your version of excel.

Gord Dibben wrote:

Correction......

In the workbook I received from Jack the number of chars were 22 and 30

Not that it makes any difference but might as well get the numbers right<g

Gord

On Sat, 13 Jan 2007 20:01:59 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

Autofit and word wrap don't work?
 

Thanks to both Gord and Dave.

Unfortunately, I am dealing with 40+ work sheets, prepared on 40+ different
machines. Modifying the registries is not a viable option. Since the word
wrap seems to work at 80% zoom, I will substitute that .

"Gord Dibben" wrote:

Thanks Dave

I did as instructed and the wrap text and autofit now work as we desire when
zoom at 60%

Have emailed Jack pointing him to your posting.


Gord

On Sun, 14 Jan 2007 06:45:51 -0600, Dave Peterson
wrote:

This is a guess...

Excel will substitute different fonts when you get less than 80% (IIRC).

Maybe this post by Debra Dalgleish will help....

You can change a registry setting to prevent this from happening (make a
backup copy of the registry first):

1. From the Start button, choose Run
2. Type regedit then click OK
3. Click the + sign to the left of HKEY_CURRENT_USER
4. Open Software, Microsoft, Office, 9.0, Excel, Options
5. Choose EditNewDWORD Value
6. Type the name for the DWORD: FontSub
7. Press Enter to complete the renaming
8. Choose EditModify
9. Type 0 as the value, select Decimal, and click OK
10. Close the Registry Editor

=====
That 9.0 will vary with your version of excel.

Gord Dibben wrote:

Correction......

In the workbook I received from Jack the number of chars were 22 and 30

Not that it makes any difference but might as well get the numbers right<g

Gord

On Sat, 13 Jan 2007 20:01:59 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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