Gareth,

Data Validation doesn't trigger the Change event in 97, but what you could
do is to set another cell linked to your data validation cell, and have a
calculate event do the work for you. I used this technique to have dynamic
data validation list in XL97.

--

HTH

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

"Gareth" wrote in message
...
My sheet ('Purchases') contains a list of medicines, the code below
provides
me with a list of medicines which haven't run out.

The list is put in column A on the 'Medicine in stock' sheet.

Sub Medicineinstock()
With Worksheets("Purchases")
For Each cell In .Range("H2:H" & .Range("H65536").End(xlUp).Row)
If Not IsEmpty(cell.Value) Then
If IsNumeric(cell.Value) Then
If cell.Value 0 Then
Worksheets("Medicine in stock").Range("A65536").End(xlUp).Offset(1,
0).Value
= cell.Offset(0, -6).Value
End If
End If
End If
Next
End With
End Sub

I want to make a data validation list out of the medicine in stock and
apply
that list to the next 20 blank cells in column A on sheet 'Medicine
Record'.

My problem is that I want to have the Change Event of that sheet work when
an item in the data validation list is picked and work has Excel '97 and I
believe it doesn't 'fire' in '97.

A previos workaround was to enter the items directly into the control, is
it
possible to do this by macro??

Thanks in advance.

Gareth