Play sound upon cell entry

gerryger

Board Regular
Joined
Apr 19, 2010
Messages
54
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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

doofusboy

Well-known Member
Joined
Oct 14, 2003
Messages
1,325
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

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
Apr 19, 2010
Messages
54
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,
 

gerryger

Board Regular
Joined
Apr 19, 2010
Messages
54
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,
 

gerryger

Board Regular
Joined
Apr 19, 2010
Messages
54
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????
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,991
Messages
5,856,689
Members
431,828
Latest member
kARTIK12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top