Unique values

  • Thread starter Thread starter Karel Delbaere
  • Start date Start date
K

Karel Delbaere

Hello,
I want to create a datasheet, but I have to be sure that I don¹t use double
values in 1 colon. The advanced filter I know, but I want to get a warning
at the moment I enter the cell.

example:

AA
BB
CC
AA --> should be refused when clicking ³enter²

Thanks
 
Try this. Copy/paste into the worksheet module reached by right clickin
the tab and selecting View Code.

'=============================================
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim FoundCell As Object
Dim FirstAddress As String
Dim ValueFound As Boolean
Dim MyValue As Variant
'-------------------------------
MyValue = Target.Value
If IsEmpty(MyValue) Then Exit Sub
'--------------------------------
ValueFound = False
FirstAddress = Target.Address
'--------------------------------
With ActiveSheet.Columns(Target.Column)
Set FoundCell = .Find(MyValue, LookIn:=xlValues
MatchCase:=False)
If Not FoundCell Is Nothing Then
Do
If FoundCell.Address <> FirstAddress Then
ValueFound = True
Exit Do
End If
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing And FoundCell.Addres
<> FirstAddress
End If
End With
'---------------------------------
If ValueFound = True Then
MsgBox ("Value already exists in cell " & FoundCell.Address)
Target.Value = Null
End If
End Sub
'===========================================
 
...
...
This page should be exactly what you need.
http://www.ozgrid.com/Excel/Formulas.htm

Under "Preventing Duplicates"
Click to expand...
...

The technique mentions traps *entry* of duplicate values, but data validation
doesn't prevent *pasting* values that fail the validation criteria. If there's a
remote possibility users would paste values into cells that need to be
validated, then there's no alternative to using Calculate or SheetCalculate
event handlers to trap such entries. I use Calculate rather than Change event
handlers because pasting doesn't trigger Change events either.

Event handlers could be disabled by disabling macros generally or disabling only
event handlers. One way to deal with that is to have two cells in the workbook
containing the formulas

=ctsu()+0

and

=ctsu()&""

Give the first one the defined name NTRAP and the second the defined name TTRAP.

Both formulas refer to the udf ctsu(), which I define as

Function ctsu() As Variant
Application.Volatile True
If Not Application.EnableEvents Then ctsu = CVErr(xlErrValue)
End Function

This function will return the #NAME? error if macros are disabled, and the
#VALUE! error if only application events are disabled. Add NTRAP to formulas
returning numbers, e.g., change =1+2 to =1+2+NTRAP. Append TTRAP to formulas
returning text, e.g., change ="a"&"b" to ="a"&"b"&TTRAP. This will force such
formulas to return error values unless macros and application events are
enabled. In my own experience, nothing works as well to discourage users from
screwing around than ensuring that they get only garbage results if they do so.

I'm not arguing against data validation, I just believe it needs to be
supplemented with [Sheet]Calculate event handlers. As for believing users would
never paste data into cells, experience has shown me than users will screw up
data entry in *every* way they're not systematically prevented from doing, and
it's only a matter of time until they do so. In other words, while it may take
geological ages for the proverbial 100 monkeys with typewriters to reproduce the
works of Shakespeare, it'll take a dozen users less than a year to locate all
data validation gaps in any application.
 
Back
Top