Play sound using VBA if an error is detected

Question

I currently have this code:

Option Explicit 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 Cell As Range Dim CheckRange As Range Dim PlaySound As Boolean Set CheckRange = Range("C:C") For Each Cell In CheckRange If Cell.Value = "#N/A" Then PlaySound = True End If Next If PlaySound Then Call sndPlaySound32("C:\windows\media\chord.wav", 1) End If End Sub 

I am trying to get it so that if there is an error in column C, an audible sound is heard, but it does not work, any ideas?

Answer 1

You do not need an API for this

You can also use Beep .

 Sub Sample() Beep End Sub 

Example

WAY 1

This code will be run if there are changes in the document anywhere

 Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim Cell As Range Dim CheckRange As Range Set CheckRange = Range("C:C") For Each Cell In CheckRange If Cell.Text = "#N/A" Then Beep Exit For End If Next End Sub 

WAY 2

Alternative to above code

 Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim Cell As Range For Each Cell In Columns(3) On Error Resume Next If CVErr(Cell) = CVErr(2042) Then Beep Exit For End If On Error GoTo 0 Next End Sub 

Way 3

If you want to check Col C only if there is a manual shift anywhere in Col C

 Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim Cell As Range If Not Intersect(Target, Columns(3)) Is Nothing Then For Each Cell In Columns(3) On Error Resume Next If CVErr(Cell) = CVErr(2042) Then Beep Exit For End If On Error GoTo 0 Next End If End Sub 

Method 4

If you want to check a specific cell, if there is a manual change in this cell.

 Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim Cell As Range If Not Intersect(Target, Columns(3)) Is Nothing Then On Error Resume Next If CVErr(Target) = CVErr(2042) Then Beep Exit Sub End If On Error GoTo 0 End If End Sub 

Path 5

Change path 4

 Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim Cell As Range If Not Intersect(Target, Columns(3)) Is Nothing Then If Target.Text = "#N/A" Then Beep Exit Sub End If End If End Sub 

FOLLOWUP (Add Comment)

The active cell will be in column b, so it should check the one on the right in column d - Sam Cousins ​​1 min. back

I think you meant Col C, not Col D. To do this, use Worksheet_SelectionChange to

 Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Columns(2)) Is Nothing Then If Target.Offset(, 1).Text = "#N/A" Then Beep End If End If End Sub 

Answer 2

Just paste the code below and run it and see if it works ...

 Option Explicit 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 Cell As Range Dim PlaySound As Boolean If Target.Column = 3 Then For Each Cell In Target If WorksheetFunction.IsNA(Cell.Value) Then PlaySound = True End If Next If PlaySound Then Call sndPlaySound32("C:\windows\media\chord.wav", 1) End If End If End Sub 

Answer 3

Change Cell.Value to Cell.Text

If there is a formula error, the cell value will be something like lines 2042, but the if statement searches for the text "# N / A"

I also suggest using only the used range instead of the entire column, as this will reduce the time it takes to start.

You can also exit it immediately if an error is detected.

Using Beep will not allow you to play any kind of sound, but it will make a sound signal and does not require an API or computer call to receive the specified audio file.

-EDIT- I just checked the code below and it seems to work correctly for me.

-EDIT2- Corrected code

 Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim Cell As Range Dim CheckRange As Range Set CheckRange = Range(Cells(1, 3), Cells(ActiveSheet.UsedRange.Rows.Count, 3)) For Each Cell In CheckRange If Cell.Text = "#N/A" Then Beep Exit For End If Next End Sub 

-EDIT3- Working copy of your original message

 Option Explicit 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 Cell As Range Dim CheckRange As Range Dim PlaySound As Boolean Set CheckRange = Range(Cells(1, 3), Cells(ActiveSheet.UsedRange.Rows.Count, 3)) For Each Cell In CheckRange If Cell.Text = "#N/A" Then PlaySound = True Exit For End If Next If PlaySound Then Call sndPlaySound32("C:\windows\media\chord.wav", 1) End If End Sub 

Answer 4

Try replacing with If PlaySound = True Then

Answer 5

I think your If statement, which checks the value of PlaySound, should be inside your For loop. The way you wrote it will only make noise if the last cell in CheckRange = "# N / A" because PlaySound holds the last value assigned to it from the loop.