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