Excel VBA sound notification play once only

Hi.. please help me in putting a vba code in my excel, it is connected to external data for real time ordering w/ 10 seconds refresh interval..i want sound notification to play once only if there is a changes in the number of order( example: J4) if cell J4 value is 0 then there must be no sound, if J4 value is increases  then there must a sound notification to play once only for every changes in cell and if decreases the value of A1 there must no sound notification...FYI cell J4 is link to other cell which count the number of orders...Thanks a lot
TGORDER4-Realtime-Tracking---Copy.xlsm
blackmasterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
If you want to post your code we could make specific suggestions.

But a general approach would be to created a STATIC variable in your timer handler that is executing every 10 seconds.  Compare the value in the static variable to the current content of A1 and have logic to take the desired actions when it changes.  After a change is detected and handled then save the current A1 value to the static variable for the next execution in 10 seconds.


»bp
blackmasterAuthor Commented:
i uploaded an attachment, timer is working and the auto refresh data..,my only problem is the sound notification to play once if cell j4 increases the value, and if decreases the value of j4 there must be no sound..i want to hear the sound only if j4 is increases the value...thanks
Martin LissOlder than dirtCommented:
Try this.
29086472.xlsm
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Bill PrewIT / Software Engineering ConsultantCommented:
Give this routine a try:

Sub my_Procedure()
    Static PriorValue As Long
    Dim CurrentValue As Long
    
    CurrentValue = ActiveWorkbook.Sheets("HMS").Range("J4").Value
    
    If CurrentValue > PriorValue Then
        Beep
        ThisWorkbook.RefreshAll
    End If
    
    PriorValue = CurrentValue
    my_onTime
End Sub

Open in new window


»bp
Bill PrewIT / Software Engineering ConsultantCommented:
One small adjustment, to make sure there is no confusion if the event fires when on a different open workbook.

Sub my_Procedure()
    Static PriorValue As Long
    Dim CurrentValue As Long
    
    CurrentValue = ThisWorkbook.Sheets("HMS").Range("J4").Value

    If CurrentValue > PriorValue Then
        Beep
        ThisWorkbook.RefreshAll
    End If
    
    PriorValue = CurrentValue
    my_onTime
End Sub

Open in new window


»bp

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill PrewIT / Software Engineering ConsultantCommented:
if decreases the value of A1 there must no sound notification

I understand the basic statement here, but can you clarify a situation?

  • Let's say J4 is at value 2.
  • It changes to value 3, and the bell sounds.
  • It changes back to value 2, and no bell sounds.
  • What should happen if it changes to value 3 now?  Should the bell sound again, or not until it goes past the previous high value of 3?

(currently my friend Martin and I have interpreted it slightly differently, wanted to determine which approach is desired...)


»bp
blackmasterAuthor Commented:
for example: if the value of cell j4 became 2, there must be  a sound to play once only..if it increase to 3 then there must be a sound also, if cell j4 value go back to 2 again there must be no sound...this sheet is connected to an external data using pivot table, i put a sound notification before and it works but my problem is everytime the worksheet refresh from external data the sound play continuously even if the value of j4 remain constant/stable .. i will give a try using the code you provided, i will feedback later...and also I'm sorry for my poor english, this is not my primary language thats why i cant explain it clearly.,thanks again sir,
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, my solution does what you described (I didn't test Martin's but I expect his does too), see how it goes.

We may still have to revisit this question though, you haven't answered that particular scenario.
What should happen if it changes to value 3 now?  Should the bell sound again, or not until it goes past the previous high value of 3?

»bp
blackmasterAuthor Commented:
yes sir, the bell should be sound again once only if cell value changes to 3 or 4 and above..example: if the maximum value of cell  j4 is 5 then the bell should be ring, if this cell value decreases to 4 then sound must be turned off....by the way I already tried your code sir bill and modified version uploaded by sir martin but the beep is not working..
Bill PrewIT / Software Engineering ConsultantCommented:
If you create a small test procedure as below in VBA editor, and run it, does the "bell" sound?

Sub TestBeep()
    Beep
End Sub

Open in new window


»bp
blackmasterAuthor Commented:
yes sir! the sounds play when i press the f5 and run testbeep..
blackmasterAuthor Commented:
i use this method using this link using method 1 and 2 and it works but my one and only problem is the beeping, it beeps continuously when data is refresh after 10 seconds..
Bill PrewIT / Software Engineering ConsultantCommented:
Odd, I tested my code here and it worked as expected.  Once the value in J4 changed within 10 seconds it would beep, and then not beep again until it changed again.

You may want to put some Debug.Print statements in the procedure and try and make sure that it is executing.  If it isn't then the timer probably isn't set, so run the timer procedure manually to set it again.  I found that if I wasn't careful when updating the trigger code and a syntax error occurred then the timer was gone until I restarted it.


»bp
Bill PrewIT / Software Engineering ConsultantCommented:
Can you upload your test XLSM file here so I can look at it?


»bp
blackmasterAuthor Commented:
Bill PrewIT / Software Engineering ConsultantCommented:
Not sure I understand why you posted that link, I was looking for the actual Excel file you are testing with my code that isn't working.


»bp
blackmasterAuthor Commented:
Hi sir bill,
i just want to say thank you for the solution you've provided in this problem..your solution works perfectly in my excel after a long retry, i can now run my realtime tracking w/ beep and custom sound...thanks again,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.