PROBLEM - HELP HELP - Prevent Duplicate entries on txt field

[dspa0712]

I have a speadsheet where Column C (C1:C5) currently holds values

C1=A
C2=FF
C3=fd
C4=gg
C5=CF

What I what I want is a peice of code to run as a check so for example when a user enters a value in C6 it will check that the value has not already been entered in (C1 toC5)..if so then a message will appear this number already has been entered.

Then if a user enters into C6 the value kj, when a new users enters a value in C7 the next time it will check whether this new value has been entered in (C1 to C6) etc etc.

It would also be ideal if the checking ignore cases...eg values like CF and cf were classed as the same and couldn't be entered.


Please Please help

 

[Lewiy]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
With WorksheetFunction
    If .CountIf(Range("C1:C" & Target.Row), Target.Value) > 1 And Target.Column = 3 Then
        MsgBox "This item has already been entered"
        Target.Value = ""
    End If
End With
End Sub

 

[dspa0712]

Cheers Lewiy

works like a dream !!!!

 

[dspa0712]

Cheers Lewiy

works like a dream !!!!

 

[dspa0712]

Cheers Lewiy

works like a dream !!!!

 

[dspa0712]

PROBLEM PROBLEM PROBLEM:

I have been informed that the values in Column C have to be Text as opposed to General or Numberic.

eg

If C6 is entered as "xx" then ok no message will run
If C7 is left blank and user populates C8 with a txt item "CF" the procedure will run and message pops up,

The procedure Lewiy wrote seems to hang the system. can this be overcome so that if the values in Column C are Text then the procedure will work. It also seems to hang the system when users miss entries eg C7 value entered then C9 entered....

This needs to be overcome as this situation may take place in practice. When different users are entering values.

 

[Lewiy]

The code should not fail because a row has been missed because it takes the full range above the target cell into account. Also, I can’t see why it would matter how the text is formatted, it should pick up anything if there is a duplicate entry already entered regardless of whether it a number, text string, date, etc. This has me somewhat confused

 

[dspa0712]

I did the following which I would like you to mirror and see if you get the same problem:

If you create the sheet with
On a new workbook

C1=A
C2=FF
C3=fd
C4=gg
C5=CF

Formated all values in column C to text then saved workbook

I then entered in C6 the value CF

Code ran and got message "This item has already been entered"
Clicked OK and it deleted C6 value

If a user then wanted to enter a value say in C8 of GG the code says "This item has already been entered" and it deletes C8 value then hangs. :x

 

[Lewiy]

Did you want it to be case sensitive? I mirrored your method and it reacted as I expected, deleting the value “GG” in C8, but the code did not “hang”.

 

[dspa0712]

I did not want it to be case sensitive

Strange did it again and I cant get rid of the mgsBox it wont close when click OK.

I think it is coming from the fact that after it deletes C6 value I then miss two or three lines leaving them blank and try to enter GG in C8.....

eg

C1=A
C2=FF
C3=fd
C4=gg
C5=CF
C6 <blank>
C7 <blank>
C8 GG


Hope this explains it.......might be something as simple as the code needing to ignore blank cells above target cell but things like this are beyond my "Young Skywalker" skills..........Need a Jedi Master me thinks