How to validate Data....

Posted by Ramki on January 03, 2001 12:03 PM

Hi,

I am trying to paste a value into a cell containing a
list of pulldown values. The value is being pasted is not
within the range of pulldown values.

If I try to edit the value in the cell containing the pulldown
values, and new value being entered is not within the
acceptable range, then an error message pops up.

But when the value is being pasted, and is not withing the
acceptable range, no validation takes place.

How did I trigger this validation when we paste a value into
the cell containing pulldown values.

Please help me with an idea.......

THanks in ADvance,
Ramki
it is in the acceptable range.

Posted by Dave on January 04, 2001 12:30 AM

Hi Ramki

I'm afraid Data Validation wont stop a user from pasting in data. You would need VBA to sop pasting.

Right click on the sheet name tab and select "View Code" and paste this over the top.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$A$1" Then
Application.EnableEvents = False
With Target
Select Case .Value
Case 5 To 20
.ClearContents
End Select
End With
Application.EnableEvents = True
End If
End Sub

Change as needed

Dave