What's wrong with this code?

[ExcelFailure]

Hello and thanks,

Does anyone know what is wrong with this code? It comes up with a compile error.

Thanks again

Private Declare Function sndPlaySound Lib "winmm.dll" _
   Alias "sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long


Private Sub Worksheet_calculate(ByVal Target As Range)
   Dim KeyCells               As Range


   ' The variable KeyCells contains the cells that will
   ' cause an alert when they are changed.
   Set KeyCells = Range("I2")


   If Not Application.Intersect(KeyCells, Target) _
      Is Nothing Then
      PlayWavFile "c:\tada.wav", False
   End If


End Sub
Sub PlayWavFile(WavFileName As String, Wait As Boolean)
    If Dir(WavFileName) = "" Then Exit Sub ' no file to play
    If Wait Then ' play sound before running any more code
        sndPlaySound WavFileName, 0
    Else ' play sound while code is running
        sndPlaySound WavFileName, 1
   End If
End Sub

 

[VoG]

Worksheet_Calculate does not have Target as an argument. It is simply

Private Sub Worksheet_Calculate()
'code here
End Sub

 

[ExcelFailure]

Hi and thanks for the reply. It's now coming up with a run time error and highllighting the following piece of code:

If Not Application.Intersect(KeyCells, Target) _
      Is Nothing Then

Any ideas? Thanks again

 

[VoG]

Target is not defined.

 

[ExcelFailure]

Ok so the target cell should go instead of the word "target"? Sorry, bit of a noob

 

[VoG]

You would need something like

Private Sub Worksheet_Calculate()
Static oldval As Variant
If Range("I2").Value <> oldval Then
    oldval = Range("I2").Value
    PlayWavFile "c:\tada.wav", False
End If
End Sub

 

[ExcelFailure]

Thanks very much, it works perfectly!