Play sound upon cell entry

[gerryger]

I have a code on my sheet that should play a sound whenever the data in column j is greater than 1. The sound plays however it plays whenever I input data manually or automatically thru a formula in all the cells. I just need it play when column j is populated. Also of note....the data in column j is populated from a formula. I can't post the code right know because I am not at my home computer. I'll post it later if it will help.

 

[doofusboy]

So it sounds like your play a sound code works and you just need to control when that code is called [triggered]. Posting what ya have should help to lead you in the right direction.

 

[Smitty]

How about a Calculate event:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>    <SPAN style="color:#00007F">Dim</SPAN> result <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        result = Application.WorksheetFunction.Sum(Range("J:J"))<br>        <br>        <SPAN style="color:#00007F">If</SPAN> result >= 1 <SPAN style="color:#00007F">Then</SPAN><br>            Beep<br>            MsgBox "Column J's value is " & result<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[gerryger]

sounds like that will work..... However I would like to play my own wav file....how do I change the code to do that instead of just 'beep'.

How about a Calculate event:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>****<SPAN style="color:#00007F">Dim</SPAN> result <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>********result = Application.WorksheetFunction.Sum(Range("J:J"))<br>********<br>********<SPAN style="color:#00007F">If</SPAN> result >= 1 <SPAN style="color:#00007F">Then</SPAN><br>************Beep<br>************MsgBox "Column J's value is " & result<br>********<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>********<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[Smitty]

See: http://www.mrexcel.com/archive/VBA/13716b.html

http://www.exceltip.com/st/Playing_WAV-files_using_VBA_in_Microsoft_Excel/460.html

 

[gerryger]

Used the code below and still am having problems with the sound being triggered by any entry in every cell. Can't get it to work with just the data being entered in J:J column. Any thoughts.

How about a Calculate event:

Private Sub Worksheet_Calculate()
Dim result As Long
result = Application.WorksheetFunction.Sum(Range("J:J"))

If result >= 1 Then
Beep
MsgBox "Column J's value is " & result
End If

End Sub

HTH,

 

[Smitty]

You'll need to post the code you came up with.

 

[gerryger]

You'll need to post the code you came up with.

I am using the following code:

Private Sub Worksheet_Calculate()
Dim result As Long
result = Application.WorksheetFunction.Sum(Range("J:J"))

If result >= 1 Then
Beep
MsgBox "Column J's value is " & result
End If

End Sub


I think I know what the problem is. When all the cells in column J are empty the code works correctly. The sound does not come on until there is an entry in cell J1, which is what I want to happen. The problem is that once there is a value in cell J1, any future inputs on the sheet, despite having nothing to do with column J, will unfortunately trigger the code. I think I need to modify the code (range perhaps) to find the next empty cell in column J. If this can be done then I guess I wouldn't need Application.WorksheetFunction.Sum and If result >= 1 lines of code. I just need the code to find the next empty cell in column J.

Seems confusing...I hope I explained what I am trying to do correctly. Thoughts????