Hi, forum
I've got a problem on the validation. Actually, The validation works fine
when I type in any info.
However, when I auto fill the list using dropdown function or I copy any
info from some where else, the validation totally dfailed ! Can anybody
tells me how could this happen ? What should I do to keep the validation
rules when copying ?

thanks in advance ~!

niou

It's a known fact that copy-paste or dragging down will defeat data
validation (DV).

If you're trying to prevent duplicate entries using DV, then you could try
Vasant's code below, which will prevent duplicate entries -- including
preventing copy-paste or dragging which would defeat data validation -- for
col A in a sheet. Entries are assumed made progressively from row1 down.

To install the code, right-click on the worksheet tab, select View Code,
then copy and paste the code below into the white space on the right. Press
Alt+Q to return to Excel. Test it out ..

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' Vasant Nanavati 2002
On Error GoTo ErrorHandler
If Not Intersect(Target, Columns(1)) Is Nothing Then
If Not Range(Cells(1, 1), Cells(Intersect _
(Target, Columns(1)).Row - 1, 1)).Find _
(Target.Value, LookIn:=xlValues, LookAt:= _
xlWhole) Is Nothing Then
MsgBox "Part no. already exists!"
Application.EnableEvents = False
With Intersect(Target, Columns(1))
.ClearContents
.Select
End With
End If
End If
ErrorHandler:
Application.EnableEvents = True
End Sub
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"x6v87qe" wrote:
Hi, forum
I've got a problem on the validation. Actually, The validation works fine
when I type in any info.
However, when I auto fill the list using dropdown function or I copy any
info from some where else, the validation totally dfailed ! Can anybody
tells me how could this happen ? What should I do to keep the validation
rules when copying ?

thanks in advance ~!

niou

Thanks max for you quick response. That code works fine for preventing dups.
The entries I have does have some dups. It seems I have to accept the fact :(

"Max" wrote:

It's a known fact that copy-paste or dragging down will defeat data
validation (DV).

If you're trying to prevent duplicate entries using DV, then you could try
Vasant's code below, which will prevent duplicate entries -- including
preventing copy-paste or dragging which would defeat data validation -- for
col A in a sheet. Entries are assumed made progressively from row1 down.

To install the code, right-click on the worksheet tab, select View Code,
then copy and paste the code below into the white space on the right. Press
Alt+Q to return to Excel. Test it out ..

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' Vasant Nanavati 2002
On Error GoTo ErrorHandler
If Not Intersect(Target, Columns(1)) Is Nothing Then
If Not Range(Cells(1, 1), Cells(Intersect _
(Target, Columns(1)).Row - 1, 1)).Find _
(Target.Value, LookIn:=xlValues, LookAt:= _
xlWhole) Is Nothing Then
MsgBox "Part no. already exists!"
Application.EnableEvents = False
With Intersect(Target, Columns(1))
.ClearContents
.Select
End With
End If
End If
ErrorHandler:
Application.EnableEvents = True
End Sub
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"x6v87qe" wrote:
Hi, forum
I've got a problem on the validation. Actually, The validation works fine
when I type in any info.
However, when I auto fill the list using dropdown function or I copy any
info from some where else, the validation totally dfailed ! Can anybody
tells me how could this happen ? What should I do to keep the validation
rules when copying ?

thanks in advance ~!

niou

welcome, niou.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"x6v87qe" wrote in message
...
Thanks max for you quick response. That code works fine for preventing
dups.
The entries I have does have some dups. It seems I have to accept the fact
:(