Prevent duplicate entries

Julee

New Member
Joined
Aug 7, 2014
Messages
42
Dear all,

I need to validate a particular cell, let's say A20, in the sheet1 that it would check all column B in the sheet2 whether there already exists such a text. If such a text already exists in column B of the sheet2, I need that it would inform the user with the MsgBox that such a text already exist. Is it possible to do this with excel VBA?


Thanks a lot!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

ChrisBM

Board Regular
Joined
Sep 22, 2014
Messages
215
Try using the on change function.

Code:
Private Sub Worksheet_Change(ByVal TargetCell as Range)
       If [COLOR=#333333]Application.WorksheetFunction.IsNA[/COLOR]([COLOR=#333333]Application.WorksheetFunction.Match([/COLOR]TargetCell.Value, Sheets("Sheet2").Range("B:B"), 0)) Then
             'Fine, no matches
       Else
             Msgbox("Already exists!") 
             'Clear Cell
             TargetCell.Value = ""
       End If
End Sub
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,873
Office Version
  1. 365
Platform
  1. Windows
Put this code in the worksheet events. Open the VBE and click on the worksheet in the VBA project window and then insert the code in the right window when it opens.

Code:
Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim lr As Long
lr = Range("B" & Rows.Count).End(xlUp).Row
    If Target.Column = 1 Then
        For i = 1 To lr
        If Range("B" & i) = Target.Value Then
        MsgBox "Duplicate"
        End If
        Next i
    End If
End Sub
 

Julee

New Member
Joined
Aug 7, 2014
Messages
42
Thank you very much for the codes, but it doesn't work.. What could be the reason?
Where do I indicate that particular cell, i.e. "A20" that I would like to validate?
 

dcofer

Board Regular
Joined
Jun 25, 2004
Messages
200

ADVERTISEMENT

Does the solution have to be a VBA solution? If not, you can use data validation.

Click on cell A20, then go to the Data tab, and then click on Data Validation.

Select Custom in the drop down box under Allow, and enter this formula in the formula box: =COUNTIF(B:B,A20)<1

This will prevent anyone from entering data in cell A20 that matches exactly any data in Column B.

You can then click on the Input Message tab to customize the message in the message box that pops up.

Hope this helps.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,873
Office Version
  1. 365
Platform
  1. Windows
Julee
It worked for me when I tested the code. Where did you place the code? Did you follow my instructions and place it in a worksheet event? NOT in a module.

This line in the code identifies that you are looking at column A

Code:
 If Target.Column = 1 Then</pre>
 

Julee

New Member
Joined
Aug 7, 2014
Messages
42

ADVERTISEMENT

I need to make a reference to the different sheet. When I select Custom in the Data Validation, it gives me an error saying that "You cannot use references to other worksheets or workbooks for Data Validation criteria".

Yes, I place it in the worksheet event indeed. However, I need a cell "A20" in sheet1 to be checked in the whole column "B" in the sheet2 if there are no duplicates. Would you be able to let me know, how both these sheets could be linked?
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,873
Office Version
  1. 365
Platform
  1. Windows
changed it up to reflect input in A20 on sheet 1 and data already in sheet 2

Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
Dim i As Long
Dim lr As Long
lr = s2.Range("B" & Rows.Count).End(xlUp).Row
    If Target.Address = "$A$20" Then
        For i = 1 To lr
        If s2.Range("B" & i) = Target.Value Then
        MsgBox "Duplicate"
        End If
        Next i
    End If
End Sub
 

kvsrinivasamurthy

Well-known Member
Joined
Nov 6, 2013
Messages
810
Sheet2 B2:B50 is reference data. Name that range as Range2 (using Name manager).
Validate Sheet1 A20 cell with
Custom
Countif(Range2,A20)=0

In Error message type

Text already exists.
 

Julee

New Member
Joined
Aug 7, 2014
Messages
42
Thank you so much, but none of them is working for me... What am I missing?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,598
Messages
5,832,643
Members
430,150
Latest member
amitk1

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