Automatically change column widths?
 

Hi everyone.

I am using code from a previous post by Mike H. that automatically fits the
cell as you tab or enter out of it. I am wondering if anyone knows how to
write it so that the 'Undo' button will still be available after leaving the
cell. Sometimes I decide that the previous cell amount was the correct one
after all, but can't use 'Undo' to fix it.

The code from Mike H is:

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

Please and Thank You!
--
smither fan

Automatically change column widths?
 

Ross,

Loss of Undo is the way Excel is designed. Almost all actions in Excel are
programmed to add their undo method to the undo list but f you want to undo a
macro then the stock answer is it's not easy and you will have to write your
own routine to do it and add it to the undo list. As an example of how easily
the undo list can be wiped out run this macro:-

Sub jjj()
Cells(1, 1).Value = 1
End Sub

You will note the undo list is gone. However if you simply type 1 into A1
then it can be undone.

Excel proves a system for writing these Undo routines for macros and if you
want to have a go check out application.onUndo in help

Mike


"Ross" wrote:

Hi everyone.

I am using code from a previous post by Mike H. that automatically fits the
cell as you tab or enter out of it. I am wondering if anyone knows how to
write it so that the 'Undo' button will still be available after leaving the
cell. Sometimes I decide that the previous cell amount was the correct one
after all, but can't use 'Undo' to fix it.

The code from Mike H is:

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

Please and Thank You!
--
smither fan

Automatically change column widths?
 

See John Walkenbach's site for undo.

http://www.j-walk.com/ss/excel/tips/tip23.htm


Gord Dibben MS Excel MVP

On Thu, 19 Jul 2007 18:54:00 -0700, Ross wrote:

Hi everyone.

I am using code from a previous post by Mike H. that automatically fits the
cell as you tab or enter out of it. I am wondering if anyone knows how to
write it so that the 'Undo' button will still be available after leaving the
cell. Sometimes I decide that the previous cell amount was the correct one
after all, but can't use 'Undo' to fix it.

The code from Mike H is:

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

Please and Thank You!