Column automatically adjusts width to fit text entries

[redsanders]

Not able to get an answer to this in General Questions Forum - so trying here:
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.

 

[Jim Cone]

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


"redsanders" <[email protected]>
wrote in message
Not able to get an answer to this in General Questions Forum - so trying here:
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 H]

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

 

[Mike H]

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]

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]

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

 

[Gord Dibben]

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

 

[Ross]

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..