In the data validation where it says list, instead of

=$A$1:$A$20

Put the values you would find in those cells
Mike,Tom,Fred,Sally,Rich,Bill,Hoover,Jamime,Lauren ce

--
Regards,
Tom Ogilvy


Gareth wrote in message
...
So having the list on a sheet doesn't work...

If there is one, what would be the easiest way to do it?

I have always put list into ranges on sheets, how do you put them into the
control?

Gareth

"Tom Ogilvy" wrote in message
...
It is pretty well established that, in xl97, the change event is not
fired
by when a selection is made from a data validation list. Debra
Dalgleish
has further qualified this to be:

Debra verified that in xl97 the event is triggered if the list is
embedded
in
the DataValidation. But won't fire if the list is a range on a
worksheet.


So apparently Sandy V entered her validation list directly in the
control.
The more common use of a range, does not work in Excel 97. The common
workaround is to have a formula reference the results of the data
validation
cell and use the calculate event, but this would fire everytime the
sheet
was calculated. Since you are offering a warning, it is clear when this
would be appropriate to show.

--
Regards,
Tom Ogilvy




Gareth wrote in message
...
Can anyone help?

I have the following code which works fine on 2000 but in work we have
'97
and the sheet event doesn't fire when selecting items from a drop down
valuidation list in column F.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 6 Then
If Target.Offset(0, -5).Value <=
Worksheets("Sheet2").Range("A1").Value
Then
If Right(Target.Value, 1) = "^" Then
MsgBox "Error, you must choose one without a roof on!"
End If
ElseIf Target.Offset(0, -5).Value
Worksheets("Sheet2").Range("A1").Value Then
If Right(Target.Value, 1) < "^" Then
MsgBox "Error, you must choose one with a roof on!"
End If
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

This check is very important, is there any other way to do it?

Thanks in advance.

Gareth