Sound & Speak

[xma]

If the number in the cell is over >10 then it should play a wav sound and if it under <10 then it should speak a word.
The code works for the A column (A1:A6) but the B column (B2) has the same wav and speech as A2.
Thank you for your help.

Private Declare Function sndPlaySound Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim strSound As String
    Dim strtalk As String
    
    If Not Intersect(Target, Range("$A$1,$A$2,$A$3,$A$4,$A$5,$A$6,$B$2")) Is Nothing Then
    
      
    strSound = Choose(Target.Row, "tada", "ding", "tada", "beep", "ding", "beep", "Drumroll")
    strtalk = Choose(Target.Row, "Dog", "Cat", "Pig", "House", "cat", "Rat", "Horse")
    If Target.Value > 10 Then
        sndPlaySound "C:\WINDOWS\Media\" & strSound, 0
    End If
      
    If Target.Value < 10 Then
    
        Application.Speech.Speak strtalk, 0
    End If
     End If
End Sub

 

[PHV]

You have to test Target.Column too.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xma]

PHV - Thank you for your help.
I put the "Target.Column" in and it still won't work right.
I am still doing something wrong and I don't see what it is.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim strSound As String
    Dim strtalk As String
    
    If Not Intersect(Target, Range("$A$1,$A$2,$A$3,$A$4,$A$5,$A$6,$B$2")) Is Nothing Then
    
      
    strSound = Choose(Target.Row, Target.Column, "tada", "ding", "tada", "beep", "ding", "beep", "Drumroll")
    strtalk = Choose(Target.Row, Target.Column, "Dog", "Cat", "Pig", "House", "cat", "Rat", "Horse")
    If Target.Value > 10 Then
        sndPlaySound "C:\WINDOWS\Media\" & strSound, 0
    End If
      
    If Target.Value < 9 Then
    
        Application.Speech.Speak strtalk, 1
    End If
     End If
End Sub

 

[PHV]

And what about this ?
...
If Target.column = 2 Then
strSound = "Drumroll"
strtalk = "Horse"
Else
strSound = Choose(Target.Row, "tada", "ding", "tada", "beep", "ding", "beep")
strtalk = Choose(Target.Row, "Dog", "Cat", "Pig", "House", "cat", "Rat")
End If
If Target.Value > 10 Then
sndPlaySound "C:\WINDOWS\Media\" & strSound, 0
End If
If Target.Value < 10 Then
Application.Speech.Speak strtalk, 0
End If
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xma]

PHV - Thank you very much.
It works - I can use the Column = 2 for any other column that I want such as 3 (C) and so forth.
I would have never thought of setting up a seperate target by column.

Private Declare Function sndPlaySound Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim strSound As String
    Dim strtalk As String
    
    If Not Intersect(Target, Range("$A$1,$A$2,$A$3,$A$4,$A$5,$A$6,$B$2")) Is Nothing Then
    
    If Target.Column = 2 Then
    strSound = "Drumroll"
    strtalk = "Horse"
     
  Else
    strSound = Choose(Target.Row, "tada", "ding", "tada", "beep", "ding", "beep")
    strtalk = Choose(Target.Row, "Dog", "Cat", "Pig", "House", "cat", "Rat")
  End If
  
  If Target.Column = 3 Then
    strSound = "Ding"
    strtalk = "Home"
     
  Else
    strSound = Choose(Target.Row, "tada", "ding", "tada", "beep", "ding", "beep")
    strtalk = Choose(Target.Row, "Dog", "Cat", "Pig", "House", "cat", "Rat")
  End If
  
  
  
  If Target.Value > 10 Then
    sndPlaySound "C:\WINDOWS\Media\" & strSound, 0
  End If
  If Target.Value < 10 Then
    Application.Speech.Speak strtalk, 0
  End If
End If
End Sub

 

[xma]

I was trying to see if the Sound & Speech could be set up for ANY cell in ANY row or column.
The code below works for column 1 & 3 but NOT for column 2.
I tried "If Target.Column = 2,3 Then" but that did not work like I thought it would.
Thank you for your help.

Private Declare Function sndPlaySound Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim strSound As String
    Dim strtalk As String
    
    If Not Intersect(Target, Range("$A$1,$A$2,$A$3,$A$4,$A$5,$A$6,$B$2,$C$2")) Is Nothing Then
    
    If Target.Column = 2 Then
    strSound = "Drumroll"
    strtalk = "Horse"
    
    Else
    strSound = Choose(Target.Row, "tada", "ding", "tada", "beep", "ding", "beep")
    strtalk = Choose(Target.Row, "Dog", "Cat", "Pig", "House", "cat", "Rat")
  End If
    
    If Target.Column = 3 Then
    strSound = "Beep"
    strtalk = "Home"
        
  Else
    strSound = Choose(Target.Row, "tada", "ding", "tada", "beep", "ding", "beep")
    strtalk = Choose(Target.Row, "Dog", "Cat", "Pig", "House", "cat", "Rat")
  End If
  
  
  
    
  If Target.Value > 10 Then
    sndPlaySound "C:\WINDOWS\Media\" & strSound, 0
  End If
  If Target.Value < 10 Then
    Application.Speech.Speak strtalk, 0
  End If
End If

End Sub

 

[xma]

I have used the "or" but it still uses in column 2 the sound and speech from Column 3.

If Target.Column = 2 Or Target.Column = 3 Then
    strSound = Choose(Target.Row, "Drumroll", "ding")
    strtalk = Choose(Target.Row, "house and horn", "Dog  and  cat")

 

[xma]

PHV - Thank you for your help.
I have been trying a lot of different combinations of code to make this work on ANY cell in ANY row or ANY column
in any part of the sheet and with each cell having a different wav file and speech.
So far all I can come up with is making the sound work but not the wav.