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

try
columns("B:Q").entireColumn.autofit
drop the with clause

-----Original Message-----
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


.

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

--
Regards,
Tom Ogilvy


"Graham Haughs" wrote in message
...
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

Your code ran unchanged for me in Excel 97.

--
Jim Rech
Excel MVP
"Graham Haughs" wrote in message
...
|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
|
|

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

--
Regards,
Tom Ogilvy



"Jim Rech" wrote in message
...
Your code ran unchanged for me in Excel 97.

--
Jim Rech
Excel MVP
"Graham Haughs" wrote in message
...
|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
|
|

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

"Tom Ogilvy" wrote in message
...
I had the same experience as Jim. Originally I did something stupid, so
just to clarify.

--
Regards,
Tom Ogilvy



"Jim Rech" wrote in message
...
Your code ran unchanged for me in Excel 97.

--
Jim Rech
Excel MVP
"Graham Haughs" wrote in message
...
|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
|
|

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

--
Regards,
Tom Ogilvy


"Graham Haughs" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
I had the same experience as Jim. Originally I did something stupid, so
just to clarify.

--
Regards,
Tom Ogilvy



"Jim Rech" wrote in message
...
Your code ran unchanged for me in Excel 97.

--
Jim Rech
Excel MVP
"Graham Haughs" wrote in message
...
|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
|
|

Many thanks Tom, that has cleared that anomaly up nicely.

Graham

"Tom Ogilvy" wrote in message
...
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).

--
Regards,
Tom Ogilvy


"Graham Haughs" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
I had the same experience as Jim. Originally I did something stupid,
so
just to clarify.

--
Regards,
Tom Ogilvy



"Jim Rech" wrote in message
...
Your code ran unchanged for me in Excel 97.

--
Jim Rech
Excel MVP
"Graham Haughs" wrote in message
...
|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
|
|