Alert the User when a Duplicate Value is Input

0

If i enter an Invoice number..etc on my Data Input Sheet it then sends the data to the Data Sheet Via Macro. Is there a Formula i can put into the (Invoice #)Cell ,that when i type in an Invoice it would Alert me that its already used?

Post Edited
Title: Title was not descriptive.
Answer
Discuss

Discussion

Yea, how do you want it to alert you? A pop-up window? A value in another cell? Conditional formatting? There are a lot of things that can be done.
don (rep: 1616) Apr 2, '19 at 4:26 am
A pop up window please. Thanks in advance.
Learn (rep: 2) Apr 2, '19 at 9:30 am
What is the best way of doing it?or what are your suggestions?  A value in another cell would also be good. Say the cell beside or on the same worksheet.
Learn (rep: 2) Apr 3, '19 at 9:24 am
Pop-up window is fine. You just need to do a simple search with a macro that will see if the value is already input into somewhere else. I'll get you a simple example in an Answer.
don (rep: 1616) Apr 5, '19 at 3:29 pm
Add to Discussion

Answers

0

Here is a macro that you can use:

Sub find_value(Target)

Dim SHT As Worksheet
Dim rFND As Range

strSearch = Target.Value

If strSearch <> "" Then

    Set rFND = Nothing

    ' Sheet2 is the name of the sheet where the value might already be contained.
    Set rFND = Sheets("Sheet2").Cells.Find(What:=strSearch, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlRows, SearchDirection:=xlNext, MatchCase:=False)

    If Not rFND Is Nothing Then

      msg_response = MsgBox("Value already exists.", vbOK)

      Target.Clear

      GoTo OutsideLoop

    End If

End If

OutsideLoop:

End Sub

It is modified from the code here: Macro to Find All text or values in the workbook

Then, in the worksheet where you want the macro to run, input this macro code:

Private Sub Worksheet_Change(ByVal Target As Range)

' You can also use Target.Column = 1 or 2 or 3 etc.
' 1 = column A, 2 = Column B, etc.
If Target.Address = "$A$1" Then

    Call find_value(Target)

End If

End Sub

This is what will run each time the user changes a cell value in the worksheet; it will only run in the workshet that you put it into.

To get to the code window for a worksheet, right-click its tab and click View Code.

You can learn more about the second macro here: Run a Macro When a Specific Cell Changes in Excel

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login