Maybe use that worksheet_change event. But that doesn't give you the dropdown
to choose from.

I put a list of valid entries on Sheet99 in A1:A10 and gave it a name of MyList.

Then I rightclicked on the worksheet tab that needs this behavior and selected
view code. I pasted this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Dim res As Variant

On Error GoTo errHandler:

'only validate changes to a single cell
If Target.Cells.Count 1 Then Exit Sub

If Intersect(Target, Me.Range("b3:b5002")) Is Nothing Then
Exit Sub
End If

Set myRng = Worksheets("sheet99").Range("a1:a10")

res = Application.Match(Target.Value, myRng, 0)

If IsError(res) Then
With Application
.EnableEvents = False
.Undo
MsgBox "Invalid entry"
End With
End If

errHandler:
Application.EnableEvents = True

End Sub





nelli wrote:

Thanks Dave.

I tried using a combobox.
I have 2 problems:

1. I would like to be able to paste valid data into the cell.
2. I want to use the sam validation for more than 5000 cells, so creating a
combobox for each of them will take up too much time.

Do you maybe have another solution to my problem?

Thanks so much for your help!

--

Dave Peterson