Audible alarm when Duplicate Condition Met

[Rick__]

Hi all,

I was wondering if anyone could help me with my Excel question, I've tried to search for the answer and have seen a couple posts but still unclear how to actually do this, and I'm still new to VBA.

I have a list of part numbers in column A, and in column B, I would like to scan in new part numbers, but when a duplicate is found (comparing A column to B column) I would like a sound / alarm to go off to prompt the user because they won't be necessary looking at the screen when scanning in these part numbers.

 

[wideboydixon]

Right click the sheet name, select "View Code" and paste in the following:

Private Declare Function sndPlaySound32 Lib "winmm.dll" Alias "sndPlaySoundA" (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long
Private Sub Worksheet_Change(ByVal Target As Range)

Dim checkRange As Range
Dim foundRow As Variant
Dim checkCell As Range

' Any cells in column B changed?
Set checkRange = Application.Intersect(Target, Range("B:B"))

' Nothing in column B - we're done
If checkRange Is Nothing Then Exit Sub

' Look at all changed cells
For Each checkCell In checkRange
    ' Is this value already in column A?
    foundRow = Application.Match(checkCell.Value, Range("A:A"), 0)
    If Not IsError(foundRow) Then
        ' Found it - play a sound
        sndPlaySound32 "C:\Windows\Media\chimes.wav", 0&
        
        ' (Optional) - highlight the cell in error in some way
        checkCell.Font.Color = vbRed
    End If
Next checkCell

End Sub

That checks for entries in column B that already occur in column A and plays a sound if it finds one. Change the location of the WAV file to suit.

WBD

 

[Rick__]

It works perfectly, thank you for your help with this. After looking at your code I doubt I would have been able to do this on my own.

 

[wideboydixon]

Cool. Glad it works for you.

WBD