excel - Play a sound with VBA if an error is found -

excel - Play a sound with VBA if an error is found -

i have code:

option explicit private declare function sndplaysound32 lib "winmm.dll" _ alias "sndplaysounda" (byval lpszsoundname _ string, byval uflags long) long private sub worksheet_change(byval target range) dim cell range dim checkrange range dim playsound boolean set checkrange = range("c:c") each cell in checkrange if cell.value = "#n/a" playsound = true end if next if playsound phone call sndplaysound32("c:\windows\media\chord.wav", 1) end if end sub

i trying if there error in column c, audible sound played, not work, ideas?

you don't need api this

you can utilize beep well.

sub sample() beep end sub

example

way 1

this code run if there alter anywhere in sheet

option explicit private sub worksheet_change(byval target range) dim cell range dim checkrange range set checkrange = range("c:c") each cell in checkrange if cell.text = "#n/a" beep exit end if next end sub

way 2

alternative of above code

option explicit private sub worksheet_change(byval target range) dim cell range each cell in columns(3) on error resume next if cverr(cell) = cverr(2042) beep exit end if on error goto 0 next end sub

way 3

if want check col c if there manual alter anywhere in col c

option explicit private sub worksheet_change(byval target range) dim cell range if not intersect(target, columns(3)) nil each cell in columns(3) on error resume next if cverr(cell) = cverr(2042) beep exit end if on error goto 0 next end if end sub

way 4

if want check particular cell if there manual alter in cell

option explicit private sub worksheet_change(byval target range) dim cell range if not intersect(target, columns(3)) nil on error resume next if cverr(target) = cverr(2042) beep exit sub end if on error goto 0 end if end sub

way 5

variation of way 4

option explicit private sub worksheet_change(byval target range) dim cell range if not intersect(target, columns(3)) nil if target.text = "#n/a" beep exit sub end if end if end sub

followup (post comments)

the active cell in column b, should check 1 right in column d – sam cousins 1 min ago

i guess meant col c , not col d. have utilize worksheet_selectionchange this

private sub worksheet_selectionchange(byval target range) if not intersect(target, columns(2)) nil if target.offset(, 1).text = "#n/a" beep end if end if end sub

excel excel-vba