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.
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

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:

Watch MrExcel Video

Forum statistics

Threads
1,123,088
Messages
5,599,664
Members
414,325
Latest member
kfg1287

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