CODEWITHSUNDEEP
excelvbaaudio
Scott Rice
Scott Rice

Reputation: 31

play sound in excel when data is entered into column

I am entering in serial numbers into excel, I already have data validation enabled and it works charmingly. However most of the users who use the system when scanning go by the beep the scanner makes even if it does not enter data into the cell itself and just go their merry way, which makes for a missed scan. I am trying to get a simple VBA or formula for an entire column that when data is entered into that column it will make a default windows sound letting the user know that data was successfully entered into that cell. Any help would be appreciated, thank you ahead of time. Keep in mind i've tried using values such as =If (B3>30, BeepOnce(),"") , however our serial numbers contain both letters and numbers i.e G18BB0070988019282

Upvotes: 0

Views: 812

Answers (1)

Asger
Asger

Reputation: 3877

Place this in your worksheet's module to monitor column B for any changes.
Depending on the length of the changed value, it plays different sounds:

  1. Standard Excel VBA Beep, result may vary!
  2. API call Beep with variable frequency and duration
  3. Excel Speech.Speak, if speech features are installed

Code with all 3 variants:

Option Explicit

Private Declare PtrSafe Function KernelBeep Lib "kernel32" Alias "Beep" _
   (ByVal dwFreq As Long, _
    ByVal dwDuration As Long) As Long

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MonitoredArea As Range
    Set MonitoredArea = Intersect(Target, Me.Range("B:B"))
    If Not MonitoredArea Is Nothing Then
        If Len(Target.Value) > 10 Then
            'Beep                   ' Excel VBA Standard
            KernelBeep 4000, 100    ' frequency, duration
            'Application.Speech.Speak "ok", True
        Else
            KernelBeep 600, 100
            KernelBeep 600, 100
            KernelBeep 600, 100
            'Application.Speech.Speak "a", True
        End If
    End If
End Sub

Upvotes: 1

Related Questions