PROBLEM - HELP HELP - Prevent Duplicate entries on txt field

dspa0712

New Member
Joined
May 4, 2007
Messages
31
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 :confused:
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Try this:
Code:
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

New Member
Joined
May 4, 2007
Messages
31

ADVERTISEMENT

Cheers Lewiy

works like a dream !!!!
 

dspa0712

New Member
Joined
May 4, 2007
Messages
31
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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284

ADVERTISEMENT

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 :unsure:
 

dspa0712

New Member
Joined
May 4, 2007
Messages
31
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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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

New Member
Joined
May 4, 2007
Messages
31
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,657
Messages
5,597,391
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top