Not able to get an answer to this in General Questions Forum - so trying he
Is it possible to have Excel automatically extend the width of a column as
text is entered. That is, instead of the text spilling over into the
adjoining cell, the column automatically increases in width as characters
are added. Answer needed
for one of our teachers ASAP - college instructor claims it can be done - I
am unable to find it anywhere.

It cannot be done.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"redsanders"
wrote in message
Not able to get an answer to this in General Questions Forum - so trying he
Is it possible to have Excel automatically extend the width of a column as
text is entered. That is, instead of the text spilling over into the
adjoining cell, the column automatically increases in width as characters
are added. Answer needed
for one of our teachers ASAP - college instructor claims it can be done - I
am unable to find it anywhere.

Your college instructor is both right and wrong, it's not automatic but try
this,

Right click the sheet tab, view code and paste this in.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:IV65536")) Is Nothing Then
Target.EntireColumn.AutoFit
End If
End Sub

If you want it to work on other sheets then you'll have to paste it in every
sheet.

Mike

"redsanders" wrote:

Not able to get an answer to this in General Questions Forum - so trying he
Is it possible to have Excel automatically extend the width of a column as
text is entered. That is, instead of the text spilling over into the
adjoining cell, the column automatically increases in width as characters
are added. Answer needed
for one of our teachers ASAP - college instructor claims it can be done - I
am unable to find it anywhere.

Ignore the last one, this is better.

Public OldRng As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not OldRng Is Nothing Then
OldRng.EntireColumn.AutoFit
End If
Set OldRng = Target
End Sub

"redsanders" wrote:

Not able to get an answer to this in General Questions Forum - so trying he
Is it possible to have Excel automatically extend the width of a column as
text is entered. That is, instead of the text spilling over into the
adjoining cell, the column automatically increases in width as characters
are added. Answer needed
for one of our teachers ASAP - college instructor claims it can be done - I
am unable to find it anywhere.

Thanks Mike! It works! Just haven't had the time (and/or talent) to learn
VB Script. Calling my teacher to share this with him.

"Mike H" wrote:

Ignore the last one, this is better.

Public OldRng As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not OldRng Is Nothing Then
OldRng.EntireColumn.AutoFit
End If
Set OldRng = Target
End Sub

"redsanders" wrote:

Not able to get an answer to this in General Questions Forum - so trying he
Is it possible to have Excel automatically extend the width of a column as
text is entered. That is, instead of the text spilling over into the
adjoining cell, the column automatically increases in width as characters
are added. Answer needed
for one of our teachers ASAP - college instructor claims it can be done - I
am unable to find it anywhere.

Tell him you wrote it, get some Kudos!! no not really it's best to study it
and understand how it works.

Thanks for the feedback.

Mike

"redsanders" wrote:

Thanks Mike! It works! Just haven't had the time (and/or talent) to learn
VB Script. Calling my teacher to share this with him.

"Mike H" wrote:

Ignore the last one, this is better.

Public OldRng As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not OldRng Is Nothing Then
OldRng.EntireColumn.AutoFit
End If
Set OldRng = Target
End Sub

"redsanders" wrote:

Not able to get an answer to this in General Questions Forum - so trying he
Is it possible to have Excel automatically extend the width of a column as
text is entered. That is, instead of the text spilling over into the
adjoining cell, the column automatically increases in width as characters
are added. Answer needed
for one of our teachers ASAP - college instructor claims it can be done - I
am unable to find it anywhere.

Mike's code is not VB Script.

It is VBA............Visual Basic for Applications and works with Office
Applications.

VB Script is a whole 'nother thing. See Windows Help for "vbscript"


Gord Dibben MS Excel MVP

On Wed, 18 Jul 2007 12:56:01 -0700, redsanders
wrote:

Thanks Mike! It works! Just haven't had the time (and/or talent) to learn
VB Script. Calling my teacher to share this with him.

"Mike H" wrote:

Ignore the last one, this is better.

Public OldRng As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not OldRng Is Nothing Then
OldRng.EntireColumn.AutoFit
End If
Set OldRng = Target
End Sub

"redsanders" wrote:

Not able to get an answer to this in General Questions Forum - so trying he
Is it possible to have Excel automatically extend the width of a column as
text is entered. That is, instead of the text spilling over into the
adjoining cell, the column automatically increases in width as characters
are added. Answer needed
for one of our teachers ASAP - college instructor claims it can be done - I
am unable to find it anywhere.

Hi Mike,

First...this is great!

A couple of questions for you:

1. I notice that the 'Undo' button is only available while typing in a cell.
It's grayed-out as soon as you leave the cell. I have in the past
accidently changed a figure in a cell and then realized I shouldn't have. If
I don't remember the old figure correctly, I might not be able to fix it.

2. Also, I'm having trouble getting cell A1 to auto-adjust. I even tried
using the select all button and then pasting the code in, but it didn't make
any difference. All other cells are behaving correctly. I'm using 2002 XP,
if that means anything.

Any ideas as to how to tweak your code to for these 2 things?

Thanks..
--
smither fan


"Mike H" wrote:

Ignore the last one, this is better.

Public OldRng As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not OldRng Is Nothing Then
OldRng.EntireColumn.AutoFit
End If
Set OldRng = Target
End Sub

"redsanders" wrote:

Not able to get an answer to this in General Questions Forum - so trying he
Is it possible to have Excel automatically extend the width of a column as
text is entered. That is, instead of the text spilling over into the
adjoining cell, the column automatically increases in width as characters
are added. Answer needed
for one of our teachers ASAP - college instructor claims it can be done - I
am unable to find it anywhere.