Display an alert if data already exists

[c3rn_nz]

Hi everyone, I require some help creating an alert please.

The situation is as follows.
- I have a workbook called Invoices
- There are two sheets in the workbook, one called Current and the other called Processed
- Users enter in pertinent details daily and then a certain user processes the data and moves (cut/paste) the entries from the Current sheet to the one called Processed.
- In column B, users enter an invoice number

I would like to create an alert for when a user enters an invoice number in any row of column B and that invoice number already exists in column B across either of the two sheets.

Ideally a pop up would be displayed, just providing a warning. The user can then choose to close the warning/pop up and continue if they wish.

Any assistance in this matter would be appreciated.

 

[ravisingh]

In the worksheet module for the worksheet 'Processed', paste the following code and you should be fine...

Private Sub Worksheet_Change(ByVal Target As Range)
    Set TRng = Target.Range("A1")
    If TRng.Column <> 2 Then Exit Sub
    TVal = TRng.Value
    CountValP = WorksheetFunction.CountIf(Range("B:B"), TVal) - 1 '-1 to exclude just entered value
    CountValC = WorksheetFunction.CountIf(Worksheets("Current").Range("B:B"), TVal)
    AlertTxt = ""
    If CountValP > 0 Then AlertTxt = AlertTxt & "This worksheet already contains " & CountValP & IIf(CountValP = 1, "entry", "entries") & vbCrLf
    If CountValC > 0 Then AlertTxt = AlertTxt & "Worksheet 'Current' contains " & CountValC & IIf(CountValC = 1, "entry", "entries")
    If Len(AlertTxt) > 0 Then AlertTxt = "Invoice no." & TVal & vbCrLf & AlertTxt: MsgBox (AlertTxt)
End Sub

 

[DanteAmor]

In the worksheet module for the worksheet 'Processed', paste the following code and you should be fine...

Hi ravisingh, The code must go in the Current sheet:

I would like to create an alert for when a user enters an invoice number

 

[Yongle]

similar but different

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Msg As String, C  As Boolean, P As Boolean
    If Target.CountLarge = 1 And Target.Column = 2 Then
        If Target = "" Then Exit Sub
        C = WorksheetFunction.CountIf(Target.EntireColumn, Target) > 1
        P = WorksheetFunction.CountIf(Sheets("Processed").Range("B:B"), Target) > 0
        If C Then Msg = Target & " elsewhere in this sheet"
        If P Then Msg = Msg & vbCr & Target & " also in Processed"
        If C Or P Then MsgBox Msg, vbExclamation, "Alert!"
    End If
End Sub

 

[Yongle]

I should have said that the above goes in sheet Current..

 

[ravisingh]

I should have said that the above goes in sheet Current..

In that case, paste the following code in the VBA module for sheet 'Current'

Private Sub Worksheet_Change(ByVal Target As Range)
    Set TRng = Target.Range("A1")
    If TRng.Column <> 2 Then Exit Sub
    TVal = TRng.Value
    CountValC = WorksheetFunction.CountIf(Range("B:B"), TVal) - 1 '-1 to exclude just entered value
    CountValP = WorksheetFunction.CountIf(Worksheets("Processed").Range("B:B"), TVal)
    AlertTxt = ""
    If CountValC > 0 Then AlertTxt = AlertTxt & "This worksheet already contains " & CountValC & IIf(CountValC = 1, "entry", "entries") & vbCrLf
    If CountValP > 0 Then AlertTxt = AlertTxt & "Worksheet 'Processed' contains " & CountValP & IIf(CountValP = 1, "entry", "entries")
    If Len(AlertTxt) > 0 Then AlertTxt = "Invoice no." & TVal & vbCrLf & AlertTxt: MsgBox (AlertTxt)
End Sub

 

[c3rn_nz]

Thank you all for your input. I'll try it tomorrow at work. Thanks

 

[DanteAmor]

In that case, paste the following code in the VBA module for sheet 'Current'

I had already commented on it in post #3

 

[ravisingh]

Caught nodding...
Anyway, both snippets of code are valid for the respective sheets

 

[DanteAmor]

It is right