Column Autofit Problem

G

Graham Haughs

I have the code below in as a worksheet procedure to automatically change
the column width to accomodate entries. It works fine Excel 2002 but when a
friend tried it in Excel 97 it would not work. Is this an Excel 97 feature
or does this code need to be written a different way to cover versions 97 to
2002. I would really value any help.

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
With ActiveSheet.Range("B12:Q30")
..Columns.AutoFit
End With
ActiveSheet.Protect
End Sub

Kind Regards
Graham Haughs
Turriff, Scotland
 
T

Tom Ogilvy

This worked for me in Excel 97:

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect
Me.Range("B12:Q30").EntireColumn.AutoFit
Me.Protect
End Sub
 
J

Jim Rech

Your code ran unchanged for me in Excel 97.

--
Jim Rech
Excel MVP
|I have the code below in as a worksheet procedure to automatically change
| the column width to accomodate entries. It works fine Excel 2002 but when
a
| friend tried it in Excel 97 it would not work. Is this an Excel 97 feature
| or does this code need to be written a different way to cover versions 97
to
| 2002. I would really value any help.
|
| Private Sub Worksheet_Change(ByVal Target As Range)
| ActiveSheet.Unprotect
| With ActiveSheet.Range("B12:Q30")
| .Columns.AutoFit
| End With
| ActiveSheet.Protect
| End Sub
|
| Kind Regards
| Graham Haughs
| Turriff, Scotland
|
|
 
T

Tom Ogilvy

I had the same experience as Jim. Originally I did something stupid, so
just to clarify.
 
G

Graham Haughs

Thanks to all your replys and yes it does work in 94 if you just type in
something in the cell, it will adjust. My range, I should have explaned in
more detail has a drop down list in every second column. When a figure is
selected in these cells, a Vlookup in the second column next to the cell
will return a value relative to that value from another area on the sheet.
In excel 2002 when the data validation drop down selects a figure the column
adjacent adjusts its width accordingly. It will not do this in 97 although
if I do a delete on a cell after some entries are made it will then adjust
them all.
Please do not spend any more time on it, I am grateful for the responses and
it may be something we live with and work around.

Many Thanks
Graham
 
T

Tom Ogilvy

In Excel 97, the change event does not fire if you select a cell from a
dropdown where the source of the dropdown is a range on a worksheet (rather
than hard coded in the definition of data validation).
 
Top