Preventing duplicate records

[R D S]

I have a userform which adds records to sheet 1.
One of the textboxes is called reference and when i change the contents of
this i want to make sure said contents arent already present in sheet1,col
C.
If they are a quick popup alert would be great so i could look into it.

Ive got this far

 

[Frank Kabel]

Hi
as a starting point:
http://www.cpearson.com/excel/NoDupEntry.htm

 

[R D S]

Hi i see then that i need to use COUNTIF.

To check if the entry in A1 appears once in col B i used
=COUNTIF(B:B,A1)=1

To check if the entry occurs more than once in the list in col B do i use
=COUNTIF(B:B,A1)>=1

And how do i crowbar this into vba?

Im thinking something along the lines of
formula = "=COUNTIF(B:B,me.reference.value)>=1"

but them im lost on the message box alert.

Thanks to anyone who can show me the way.
Rick

 

[icestationzbra]

try this piece out. i have found a couple of glitches, i hope you can
...

it works under the premise that you will have your masterlist in colum
B, and the data from the userform will be entered in column A. if any o
the values entered in A matches that in B, it will pop up a message an
take the cursor back to the cell which has just been entered.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Value = "" Then Exit Sub
If Target.Column = 2 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

Dim i As Long, j As Long
Dim rngDB As Range
Dim rng As Range
Dim flag As Boolean

Range("b1").Select
Range(Selection, Selection.End(xlDown)).Select
j = Selection.Rows.Count

If j = 1 Or j = 65536 Then
i = 1
Else
i = Selection.Rows.Count
End If

Target.Select

Set rngDB = Range("b1:b" & i)

For Each rng In rngDB

If Target.Value = rng.Value Then

MsgBox "Duplicate value exists"
flag = True
Exit For

Else

flag = False

End If

Next rng

If flag = True Then

Target.Select

Else

Target.Offset(1, 0).Select

End If

End Su

 

[R D S]

Hi, the data is being entered into a vba form into a textbox called
'reference'.

 

[R D S]

Hi,
sorry wasnt paying full attention there, the reference ends up in B2, and
could do with eing checked against the rest of column B, which it would i
change to achieve this? or as my last post if i could check it 'live' as i
enter it into 'reference' textbox this would be easier.

Best regards,
Rick

Hi, the data is being entered into a vba form into a textbox called
'reference'.

 

[icestationzbra]

in that case, the code will have to be taken out of the current module
which is worksheet_change, and placed into the textbox's event. it i
all quite easy if you know a little bit of VBA... but it is a littl
difficult to explain it.

if you are using a textbox called 'reference', in places where there i
'target.value', you could compare with 'reference.text'.

when you take it out of this event and place it into another, you wil
also have to remove all mentions of 'target'.

i guess the reference ends at B2 because there is a blank cell afte
B2. i was working under the premise that all the data in column B wil
be in contiguous regions, without an empty cell in between. that wa
one glitch that i was hoping that you would not find :-D... i was no
able to figure that one thing out for now. but if you have data i
contiguous regions, that will not be a problem