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!
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

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
5,846
Office Version
  1. 2019
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
199

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
5,846
Office Version
  1. 2019
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
5,846
Office Version
  1. 2019
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
642
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,612
Messages
5,523,896
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top