Trading Macro

mamos

New Member
Joined
Apr 26, 2011
Messages
5
I have dynamic data that is automatically updated by a live feed. It updates cells m3:m32.

I currently have a code that plays a sound whenever a value in any of the cells is greater than a certain threshold and another code that plays when any of the cells goes below a certain threshold.

The problem is the sound continues to play each time the live feed is updated and the cell condition is correct.

I want the sound to play once for example when a cell value first goes less than 4, but then dont play again if it continues to be under 4. However, if a new cells value goes to less than 4, I want the sound to play.

The problem at the moment is my code limits the sound to play once but it applies to the total cell range not the individual cells so if a 2nd cell's value drops below 4 it wont play.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
This is code below:

Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long
Private m_lngReset As Long

Function Alarm(Cell, Condition)
Dim WAVFile As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
On Error GoTo ErrHandler
If Evaluate(Cell.Value & Condition) And m_lngReset < 1 Then
WAVFile = ThisWorkbook.Path & "\cashtill.wav"
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = True
m_lngReset = m_lngReset + 1
Exit Function
End If
ErrHandler:
Alarm = False
End Function
 
Upvote 0
Did you receive an answer to this? I have a very similar issue. Thanks.

Both of our Function syntax is the same. I call my function from the worksheet that monitors a RTD link; which triggers the Function once the requirement is met.

My problem is that I one want the sound to play once 2 minutes. In other words, once sound plays, I don't want it to play again for minimum 2 mins, not every time the RTD updates and the condition is met.

I assume you create a Loop in a Macro, then reference the function; but I'm really just guessing... not a programmer.
 
Upvote 0

Forum statistics

Threads
1,224,566
Messages
6,179,558
Members
452,928
Latest member
101blockchains

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
Back
Top