I was wondering if there was a way to (via macros) enter text into a cell,
and then resize that cell to accomodate the text? Sometimes I enter text
into cells and the text is larger than the default size of a cell, and I
would like to have code that adjusts the size of the column to accomodate the
entire text (so that I don't have to do it manually).

Is there a fast way to do that? Sample code would be greatly appreciated.
Thanks in advance.

-Mike

This is from Excel help file for row and column size.

Tip To autofit all columns on the worksheet, click the Select All button
and then double-click any boundary between two column headings. Or select all
columns, point to Column on the Format menu, and then click AutoFit Selection.



"straightedge32" wrote:

I was wondering if there was a way to (via macros) enter text into a cell,
and then resize that cell to accomodate the text? Sometimes I enter text
into cells and the text is larger than the default size of a cell, and I
would like to have code that adjusts the size of the column to accomodate the
entire text (so that I don't have to do it manually).

Is there a fast way to do that? Sample code would be greatly appreciated.
Thanks in advance.

-Mike

You can put this code in the code window for the sheet you want this
functionality on...

*************** START OF CODE ***************
Dim ColWidth As Double

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
Application.ScreenUpdating = False
.EntireColumn.AutoFit
If .ColumnWidth <= ColWidth Then
.ColumnWidth = ColWidth
End If
Application.ScreenUpdating = True
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ColWidth = Target.ColumnWidth
End Sub
*************** END OF CODE ***************

This will leave the column's width alone if the text you enter into the cell
is smaller than, or equal to, the current width of the column; however, if
you enter text that is longer than the width of the column, the column will
resize to fit the text. However, note that the opposite is not true... if
you change the text in the cell containing the widest text string to a text
string that is shorter than its current width, the column will NOT shrink
down to the new maximum width. The reason I did it this way is because
simply auto-fitting the column would shrink it down to the size of the
smallest text string which could be too narrow for your purposes. If that is
not acceptable, then let me know exactly how you want it to function and I
will change the code accordingly.

Rick


"straightedge32" wrote in message
...
I was wondering if there was a way to (via macros) enter text into a cell,
and then resize that cell to accomodate the text? Sometimes I enter text
into cells and the text is larger than the default size of a cell, and I
would like to have code that adjusts the size of the column to accomodate
the
entire text (so that I don't have to do it manually).

Is there a fast way to do that? Sample code would be greatly appreciated.
Thanks in advance.

-Mike

Can you make this apply to only 1 or 2 columns in a worksheet?
Thanks

"Rick Rothstein (MVP - VB)" wrote:

You can put this code in the code window for the sheet you want this
functionality on...

*************** START OF CODE ***************
Dim ColWidth As Double

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
Application.ScreenUpdating = False
.EntireColumn.AutoFit
If .ColumnWidth <= ColWidth Then
.ColumnWidth = ColWidth
End If
Application.ScreenUpdating = True
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ColWidth = Target.ColumnWidth
End Sub
*************** END OF CODE ***************

This will leave the column's width alone if the text you enter into the cell
is smaller than, or equal to, the current width of the column; however, if
you enter text that is longer than the width of the column, the column will
resize to fit the text. However, note that the opposite is not true... if
you change the text in the cell containing the widest text string to a text
string that is shorter than its current width, the column will NOT shrink
down to the new maximum width. The reason I did it this way is because
simply auto-fitting the column would shrink it down to the size of the
smallest text string which could be too narrow for your purposes. If that is
not acceptable, then let me know exactly how you want it to function and I
will change the code accordingly.

Rick


"straightedge32" wrote in message
...
I was wondering if there was a way to (via macros) enter text into a cell,
and then resize that cell to accomodate the text? Sometimes I enter text
into cells and the text is larger than the default size of a cell, and I
would like to have code that adjusts the size of the column to accomodate
the
entire text (so that I don't have to do it manually).

Is there a fast way to do that? Sample code would be greatly appreciated.
Thanks in advance.

-Mike