VBA Code works in 2000 not 97
 

Hi All,

I have written the following code in the appropriate sheet object so that
when the user changes an entry in cell E21 the code is executed. It works
fine with Excel 2000 but not Excel 97. Unfortunately the workbook is to be
distributed on a network on which all users have Excel 97 !

The value in cell E21 is data validated using the list method.

By a process of elimination it appears the data validation is causing the
problem because if l remove the data validation and enter "Headcount"
manually the code executes.

Does anybody have any ideas on how to overcome the problem in Excel 97 ?

All suggestions gratefully received.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$E$21" Then
If Range("E21").Value = "Headcount" Then
Range("M26:M60").Select
Selection.Copy
Range("E26").Select
Selection.PasteSpecial Paste:=xlValues
Range("C4").Select
Application.CutCopyMode = False
Else
Range("E26:E60").Select
Selection.ClearContents
Range("C4").Select
End If
End If

Regards

Michael Beckinsale

VBA Code works in 2000 not 97
 

Michael,

You are right, the problem is Data Validation.In Excel97, changes to a cell
that uses Data Validation does not trigger the worksheet_change event.

I had this problem with dependent lists, and the way I got around it was to
put my code in the worksheet_calculation event, and in another cell (hidden
off to the cell) I put a simple reference back to the DV cell. So, when the
DV cell changed, my dummy cell also changes, which triggers the
worksheet_calculation, which executes my code.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Michael Beckinsale" wrote in message
...
Hi All,

I have written the following code in the appropriate sheet object so that
when the user changes an entry in cell E21 the code is executed. It works
fine with Excel 2000 but not Excel 97. Unfortunately the workbook is to be
distributed on a network on which all users have Excel 97 !

The value in cell E21 is data validated using the list method.

By a process of elimination it appears the data validation is causing the
problem because if l remove the data validation and enter "Headcount"
manually the code executes.

Does anybody have any ideas on how to overcome the problem in Excel 97 ?

All suggestions gratefully received.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$E$21" Then
If Range("E21").Value = "Headcount" Then
Range("M26:M60").Select
Selection.Copy
Range("E26").Select
Selection.PasteSpecial Paste:=xlValues
Range("C4").Select
Application.CutCopyMode = False
Else
Range("E26:E60").Select
Selection.ClearContents
Range("C4").Select
End If
End If

Regards

Michael Beckinsale