Play wav file when cell in column equals

[Mackey1315]

Hello all,

I have been working on a stock watchlist with different criteria that will notify me when stock prices, etc reach a certain value. I pulled the VBA code below from a site but I don't know how to make the macro work when "any cell in range L3:L24 = "Alert", then play tada.wav located in the windows/media folder. Thank you in advance for helping or directing me where to look.

I have four alerts in I3 (price, volume, bid price, ask price) and two in J3 (>=, <=). When an alert is triggered cells in column L will show "Alert" or "False"


VBA CODE:

Please Login or Register  to view this content.

[kev_]

Try replacing your change event macro with this one:

Note that the "Alert" test is case sensitive.

Please Login or Register  to view this content.

[Marc L]

Hi !

Code must be pasted to worksheet module (not to a standard one) :

Private Declare Function PlaySoundA& Lib "winmm.dll" (ByVal lpszName$, ByVal hModule&, ByVal dwFlags&)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect([L3:L24], Target) Is Nothing Then _
        If Target.Value = "Alert" Then PlaySoundA "C:\windows\media\tada.wav", 0&, &H20001
End Sub  

Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

[Mackey1315]

Gentlemen,

I thought this was fixed but it is still not playing the wav file more than once. I have put the code in the Sheets, Workbook, and made a Module in the Workbook to no avail. I'm sure I'm missing something very simple and I'm overcomplicating it.

[Marc L]

And at least if you just follow the unique direction before my code ?!

No issue on my side, the sound occurs each time a cell is changed. Try manually to mod a cell …

[Mackey1315]

Marc, I did exactly what your unique directions stated and it's not repeating the noise even if I manually delete and retype Alert.

[Marc L]

As I have no issue on my side : each time I mod a cell within range L3:L24 with "Alert" the wav is well played.

&H20001 of original code seems superfluous, try 1& instead …

Try the event with MsgBox statement instead of playing a sound …

[Mackey1315]

Marc,

It works when I manually mod the cells, but it doesn't seem to work when the API values change from Alert to Alert. Example is the volume on a stock is already >= my criteria but increases which should refresh the Alert. It appears the cell doesn't actually update.

[kev_]

@Mackey1315 & @Mark L

The code from Mark L in post#03 works without any problems for me too.

@Mackey1315
Are you using Excel 2003 or 2010?
Do you have any more event macros in that sheet? Mark's code ran once and then stopped working - has another macro left events disabled?

Please Login or Register  to view this content.

and so no event will be triggered.
If you have another Event macro (eg Selection event) on the same sheet it may be conflicting.

This macro enables events again

Please Login or Register  to view this content.

before trying again

ref your earlier comment = "I have put the code in the Sheets, Workbook, and made a Module in the Workbook to no avail"
Please check all those place and delete all traces of this code - there may be a conflict.

Then create a NEW worksheet and put Mark's code in that worksheet's module
Then enter "Alert" into one of the cells in that range
and then again - it should work!

I have attached a file with both my code and Marks - mine is on sheet1 and Marks is on sheet2 - it may help.
Both work without any problems for me.

[Mackey1315]

Kev & Marc,

I didn't have any other macros anywhere else. I have attached the file to show you my issue.

The codes work perfectly if I manually type in Alert, press Enter, Alert, press Enter, etc. When the API updates and a cell already had Alert it won't activate again. Since you don't have the API feed try changing H3 from 0 (false) to 1 or greater, then change again to another number to keep it true. Each time the value is true I want to wav to play.

[kev_]

@Mackey1315

I may have a workaround.
How frequently does your API feed update?
How many rows of data are there?

[Mackey1315]

It updates constantly and there are 22 rows of data (L3:L24). I now see that if a formula is in the cell then the macro doesn't notice it updating.

[Mackey1315]

I came across this site which explains the issue I might be having.

http://stackoverflow.com/questions/1...d-by-a-formula

[Marc L]

It's a classical issue with a code grabbed on Net : it often not for the same purpose !

When the API updates and a cell already had Alert it won't activate again.

So it is just a basic logic, warming a couple of neurones :
why don't you just remove "Alert" from column just before API updates ?‼

Or after each update copy data elsewhere so for next update you should be able to compare
new and previous data to play if needed the wav file …

[kev_]

Ha that's funny! My workaround is on much the same lines. I should have asked google!

I have been testing to see what would happen if vba copied the new data (a few seconds after the API update) to cells that a change event macro is monitoring etc etc.. - still testing but it looks promising. Will get back to you tomorrow on this one. It's time for some food!

[Marc L]

Bon appétit !

[Mackey1315]

Gentlemen,

Thank you for the help so far. I'm starting to see the dynamics between VBA and Excel.

[kev_]

The answer to this puzzle is much simpler than we thought and does not even involve a change event macro.

@Mackey1315 - you said that you wanted to be notified by Wav whenever the value in a cell in column H was True
- so we need a macro to keep carrying out that check repeatedly.

This macro
- checks the values in the appropriate cells in column H
- if it finds a True it plays the Wav and then speaks out the row number
- after the set time (here = 1 minute) calls itself again

Please Login or Register  to view this content.

[Mackey1315]

Kev_,

The column is L which is an easy enough fix, but I want the wav to play when an Alert is updated. If Alert is triggered (eg. Stock volume trades over x amount) then the wav file will play the first time. If the volume doesn't increase then I do not want it to play again after 1 minute. Only play the second time if the volume were to increase again hence triggering the Alert again.

Did my explanation make since? Sorry if I've confused matters worse.

[kev_]

Deal with it like this (within the macro in post#18), simply add a 2nd test

- create a new column to hold Previous Volume (say column ZZ)
- at trigger perform check
IF new Volume > Volume in ZZ Then play the WAV
Else don't play the WAV
- then overwrite the values in ZZ with new Volume

If you need help to write the macro, let me know - I will be back on about 3pm UK time tomorrow

Kev

[Mackey1315]

I can write formulas all day, but I definitely need help with the macro. I am trying to learn VBA, but don't really know where to start. That is a perfect idea about doing "rechecks". I will need one for Price, Bid Price, Ask Price, and VWAP as well.

Since volume can only go up > previous volume works. The other four would have to use the >= or <= qualifier compared to the previous cell matching the qualifier in cell J.

Example: Current real-time price of stock is 5

-If PRICE (first qualifier combobox I3) <= (second qualifier combobox J3) 6 (user input in K3) triggers "Alert".

-The next stock price trade is 5.05, so it's still <= 6 (in K3) which would trigger another Alert.

Take your time, and again thank you for helping me work through this.

[kev_]

I need to give this some thought - to avoid it getting messy.
We will do everything one "Alert" at a time and make sure each one works before moving on to the next.

Tell me - are all these "Alerts" going to trigger a sound? It's going to be a noisy environment

[kev_]

Having examined your workbook and considered post#21 here are my thoughts & questions

1Let's use Columns AB to AH to hold the " latest"previous values of each one of the column B,C,D,E & H
(will be used to compare against new Latest values)
2 How often are the conditions in I3 and J3 updated?
3 Same question for K3
4 I presume trigger comes from ongoing comparison of values to see if "Alert" condition has been met
5 Given that Price, Bid Price, Ask Price, and VWAP can change in both directions - I would have expected their test to be <> (not equal to ) the user value in K3 - otherwise check is in in one direction only

Kev

[Mackey1315]

kev_

2 How often are the conditions in I3 and J3 updated? - Not often, maybe once a day or every few days.

3 Same question for K3 - This is updated a little more often as I set new levels to be alerted.

4 I presume trigger comes from ongoing comparison of values to see if "Alert" condition has been met - That is correct.

5 Given that Price, Bid Price, Ask Price, and VWAP can change in both directions - I would have expected their test to be <> (not equal to ) the user value in K3 - otherwise check is in in one direction only - The reason there is = for > and < is if the stock touches your limit then starts moving away. I still want it to Alert me.

[Mackey1315]

Kev_

You are right it is a noisy environment, but I normally put the alerts based on price and on volume for stocks that don't trade that much "by appointment". I has a very expensive program that would make sounds and I stopped using it and now I realize just how much I used the Alerts.

In theory this seemed like a simple idea. I had no idea that macros don't update when a format is in a target cell

[kev_]

This relies on you wanting the sound to happen whenever there has been an API update and one of the cells in range L3 to L24 contains the value "Alert"

Each time the API updates hit, workbook formulas are automatically recalculated.
By entering this function's formula into a cell:
- the sound plays when BOTH
(a) Excel auto recalculates AND
(b) the value of any cell in the range L3 : L24 contains the value "Alert"

You need to:
1. Type this function into a general module

Please Login or Register  to view this content.

2. Then, enter this formula into any cell in the worksheet

Please Login or Register  to view this content.

3. Wait for the next update where one of the cells = "Alert" and see if the Wav is audible

[Mackey1315]

I tried changing the volume in cell H3 so that the Alert would disappear then changed it again to reappear and I received a Compile Error. The error highlights PlavWave in the new macro that goes into a general module. I placed the =MyAlert() in O1. I have reattached the file to show you the error.

[kev_]

PlayWav is in a sheet module whereas function MyAlert must be in a general module (otherwise it will not work). So we need to tell VBA where to find PlayWav
2 ways to fix it (I would choose option 2):

1. Either move PlayWav and its constants etc to the general module (= the default place VBA will look for it)
OR

2. Amend the line in MyAlert (= to tell VBA where it is)
from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

or even

Please Login or Register  to view this content.

[Mackey1315]

Kev_,

Going with option 2 worked perfectly. Maybe too perfectly, lol. I am noticing with only one Alert in the column it is playing the wav file when any of the stocks volume increases not the one with the Alert. That particular stock hasn't traded in a few minutes and the volume is static.

Example: The first stock I have the volume at 300K and the qualifiers are >= 1, so that is true - Alert. But, other stocks are trading and they are not alert, b/c I have them set at <= 0, false. But the wav keeps playing when any stocks trade.

[kev_]

It is playing every time there is a refresh AND value "Alert" appears in the column.

Let's create a copy of the latest position at the time the sound is played and compare it against the latest values, and then update a secondary "Alert" column that reflects that check.
Under which circumstances you do not want the sound to play even if the value is "Alert" - ie exatly what must be the same as before to tell VBA to ignore the alert.
Thanks

[Mackey1315]

kev_

Good question. I'll give two scenarios of when I do and do not want an Alert.

1. Alert if any of the qualifiers are true (Alert). So if the first stock in row 3 has volume of 10 and the qualifiers are >= 1; it should sound the Alert. If the volume of the same stock then updates to 20 it should refresh the Alert and sound again.
1a. If the stock in row 4 has volume is 50 and the qualifier is >= 2 then it should sound the Alert (wav) one time.

2. Using the example in 1. the Alert would have sounded twice.
2a. If the stock in row 4 has volume of 50 but the qualifier is <= 2 this should be false so no Alert.
2b. Let's assume the next update is row 4 and the volume now goes to 100. The qualifier is still <= 2 so it is still false but it plays the sound .

It still plays b/c of the Alert in row 3. Even though row 3 hasn't changed it's volume.

So I think my use of the formula has messed this up unless a formula could be put into a macro so then when an update happens a new Alert will refresh in the same cell.

I don't want it to play if any stock updates except for the one that should be Alerted.

[kev_]

I have arrived at a flexible method to make this work for you. I need to prove it first and will post it this evening .
But before that, some more Xmas lunch duties beckon....

[kev_]

Give this a try - it worked correctly every time when I tested it
***remember to amend PlayWav line (as before) so that VBA knows where to find it
- delete any other versions of the function first!
- test with only the one condition before getting more sophisticated!
- ONE secondary condition included - if the Latest Volume is the same as the Previous Volume then cancel alert
- comparison values are held (off the worksheet) in arrays
- anything written to the worksheet triggers the sound!
- constants used instead of hard-wiring the row numbers throughout - makes it easier to amend if range extended etc

If you are struggling to make it work, I'll post a workbook - but it's better that you learn by doing.

Your instructions
After pasting in the function into a general module, type this formula into a cell away from the data:

Please Login or Register  to view this content.

- that cell's value should show as "Alarming"

Hopefully the Wav will play when expected

How the function works
1. The function is triggered when the workbook recalculates
2. Three arrays are created
- cancelALERT = alerts cancelled by secondary conditions
- oldVOL = "previous" volumes
- newVOL = "latest" volumes
3. "Cancel" is put in cancelALERT if previous volume = latest volume
4. "Alert" sound is played wherever the value in columnL is "Alert" unless the value cancelALERT over-rides it

So the secret to getting a sound at only the desired time is in how any secondary conditions are expressed

Please Login or Register  to view this content.

[kev_]

@Mackey1315
Please test post#33 first - to prove that it can give you what you want with a simple set of secondary conditions. (I have a feeling that trying to work your conditions into the VBA could be a little messy )

So, on reflection, it may be easier for you (and me!!) if you build your secondary conditions via formula into your worksheet. It is a lot easier to see that something is giving you exactly the correct outcome if you can see it with your own eyes as you test. And it leaves the VBA much cleaner and future amendments easier to make perhaps. A good general rule I try to follow is if Excel can do it, then make Excel do it!

Have a look at the attached workbook, where I have illustrated my suggestion in a few lines. Area of interest is rows 100 onwards.
1. Lines 50 onwards = Previous values (which would be tossed in by VBA each time the macro is triggered)
2. Lines 100 onwards = Differences between old and new values PLUS secondary condition formula where:
- column I is the value from the Primary Alert in rows 3 to 24
- columns J to M include 2 secondary conditions
- column O = when VBA would play the alert hooter

This would allow you to mix and match your conditions exactly as you want - the end result in columnO is the key.
(- eg you could have an over-riding condition that no matter what is in the other columns if a particular condition is true then column O value = "Alert" )

My layout is for illustration only. You may prefer a wide worksheet where you extend columns out to the right on rows 3 to 24 - giving you one single extended table.

Your thoughts?

[Mackey1315]

Kev_,

I'm working on the last two posts. Sorry for the delay, for some reason I stopped receiving email notifications and just assumed you had stopped for the holidays. I'll be back later today or tomorrow with an update.

[kev_]

No problem. The site was misbehaving badly a few days ago - probably explains what happened to your notifications.