Preventing duplicate records

R

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
 
R

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
 
I

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

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

R D S said:
Hi, the data is being entered into a vba form into a textbox called
'reference'.
Click to expand...
 
I

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
 
Top