Microsoft Excel Private Sub Worksheet_Change(ByVal Target As Range)

Phil,

I presume that the constantly changing values in columns B and D are coming from some external feed. Unfortunately, these feeds are typically seen by Excel as simply being a formula update and so the Change event is not triggered.

The only solution that I know of is to use a Worksheet_Calculate macro which "monitors" the cell it question. Every time the worksheet calculates, the macro runs. It compares the current value of the cell to its previous value. If they're the same then it ends without doing anything. If the cell's value has changed, it does whatever it is you want and stores the new value elsewhere (this is the "previous value" mentioned above).

I've produced such macros before, let me know if you'd like me to go ahead with this.

Regards,
Brian.

Hi Brian,

Thanks so much for your input. You are correct, the data in column B and D is coming in via a DDE link to the Interactive Brokers database.

 I would appreciate it very much if you would go ahead and produce code to do this.

Thanks again for your help!

Regards,

Phil

Phil,

Sure. Is your file only checking a single cell (based on the two other values) or are checking a number of securities? Can you post the sheet here, please?

Thanks,
Brian.

Hi Brian,

I'm checking a number of symbols. Sometimes I'll have as many as 100. I wondered if I should have been more explicit about that.

I attached a copy of the sheet I'm working with. I'd actually like to have an audible warning when any of the three columns trigger.

Thanks so much!

Phil

Trade-20111104-Sample.xlsm

Phil,

Please see attached. I'm lazy and don't check how many rows - I just assume 200.

Regards,
Brian.
Trade-20111104-Sample.-V2.xlsm

Thanks for your quick response... How do I get the Calculate event to fire? The worksheet linked to my database but I'm not getting any beeps as they trigger.

Thanks again!!

Phil

Phil,

To test it, I manually copied B27:D27's values to B5:B27. Could you try this, please and see if it beeps.

One thing, I always disable Events while processing an event. If you had either disabled Events yourself or stopped my Calculate macro then Events may be disabled. The Auto_Open automatically enables Events. So you might try closing and re-opening the file.

Regards,
Brian.

Brian,

What I did to test is just type in lower values into column B that force the last price to be lower than my trigger price. As I do this, my red text messages are displaying but I don't hear any beeps.

Thanks again!

Phil

Phil,

I just did what I described above and it beeped. In case I misunderstand the requirements could you please do exactly what I described - that way we can be sure we're on the same page!

I have to ask - are your speakers muted ?  :)

Regards,
Brian.

Brian,

Hmmm, I'm not sure what's going on here. My speakers are not muted as I'm hearing beeps on other applications. I did as you said, "Copied B27:D27's values to B5:B27. The text in red appeared but no beep. What I was doing to test is just change the price in column B to a price lower than my target and it should also beep. The red text is appearing as above but no beep. Is yours beeping when you just type in a value in column B?

Thanks again!

Phil

Phil,

Yes, I just typed 1 in B5 and it beeped. Please see attached. It's the same as V2 except that it displays a message at various stages as the macros run.

You should get...
Auto_Open
Refresh_Old_Condition_Values
Then type 1 in B5...
Calculate Start
Calculate Change in Row 5
Calculate Beep

Regards,
Brian.
Trade-20111104-Sample.-V3.xlsm

Brian,

Thanks again for all your efforts. I'm sorry for my ignorance but I still don't know what's going on here. When I opened your new file, your code started to execute and after I answered Excel's message about updating links, your "Calculate_Start" displayed. After clicking it two or three times, I heard a beep. "Calculate_Start" continued to pop up and it was about 50-50 whether I heard beeps. Sometimes it would beep many times in sequence, and others, no beep.

To make it simple, what needs to happen if a value in column B changes to a value lower than my target column "D", it should beep along with my red text display. When I manually force a number in column B to be lower than Column D, my red text appears but I hear no beep.

I wonder if there is some setting I need to make in Excel options to enable this functionality.

Thanks one more time!!!

PHil

Phil,

Well, you've apologised for your ignorance and, as I'm a human being, I guess I'll have to reciprocate! :)

Seriously, this is a two way street and if something I propose isn't working then I share (or even own) the responsibility.

OK, let's try to break this down. Could you do the exact steps I mention in my previous post and let me know exactly what happens, please?

Regards,
Brian.

Phil,

Oops, an example of my ignorance - I should have asked you to replace all of the formulas in columns B to D by values. Your feed is doing its job and muddying the water!

Regards,
Brian.

Phil,

Yet another example of my ignorance - markets are probably too volatile for you to get a chance to replace the formulas. I've done it for you in the attached (and coloured the cells red to remind you that I've killed the formulas).

Regards,
Brian.
Trade-20111104-Sample.-V4.xlsm

Brian,

The first thing that happens is I get a security warning to "Enable Content". Then "Auto_Open" appears. I click and then "Refresh_Old Condition Values" comes up. I click and then "Calculate_Start" appears. Then I get "Calculate-Change in Row 5"..And then it keeps displaying the same form, incrementing the row by one each time. It's a loop so I do a control Break to exit.

I then Clicked "Enable Content", closed and reopened the file. The same sequence starts again. Some time during that process, it asks if I want to reestablish links. I've tried it both ways.

I'm not sure where to go from here.

Thanks one more time.

Regards,

Phil

Brian,

I just tried version 4 and after "Calculate Start" appears, it keeps coming up. I clicked it about 50+ times and most times, no beep. But sometimes there is one. However, I don't see any text changes in my warnings columns. If it would be easier to talk this over by phone, my number is [REMOVED BY JUSTAMOD]

Thanks again for all your efforts.

Regards,

Phil

Phil,

Wow! It shouldn't make any difference, but could you restart Excel, please. Close any other files open in Excel and then open V4 and change B5 to 1. Let me know what happens.

Thanks,
Brian.

Phil,

Crossing posts, I only just noticed your previous post now. We'll keep a phone call up our sleeves for the moment, but I don't think you should be posting your phone no. so I'm requesting that post to be deleted.

Regards,
Brian.

Brian,

Ok, we're making progress. I opened up the file, typed a value in Column B and with me clicking through 3 message boxes, it beeped. I modified your code to only beep if the value in column B is less than column D and that works also. The only thing we need now is to stop the beeping after the warnings are set the first time. Otherwise, it will beep continually as values are refreshed from the broker's DB.

Maybe closing and reopening Excel fixed it. One thin interesting is when I have no open Excel files and click on the excel file to open it, Excel 2007 runs by default. This happens even if the file was run by Excel 2010 the last time.

Thanks again here. You're tremendous!!!

Regards,

Phil

Phil,

Great! A few things...
(1) "I modified your code to only beep if the value in column B is less than column D"
My mistake, I thought you were using Columns G:I to make the decision on the beep ("I'd actually like to have an audible warning when any of the three columns trigger"). That's being done in the line below, so you might want to check your change doesn't conflict with it...

If xCell1.Offset(0, 5) <> "" Or xCell1.Offset(0, 6) <> "" Or xCell1.Offset(0, 7) <> "" Then

Select allOpen in new window

(2) "...it will beep continually as values are refreshed from the broker's DB"
Only if the values actually change (that's why the older values are stored in "Old_TRADE". Are you suggesting that once a Buy has been notified, prices changes for that stock shouldn't Beep again (presumably unless the Buy has gone in the meantime)?
(3) I'm afraid the only advice I can give you on having multiple versions of Excel installed is don't! Idle curiosity - 2007 and 2010 being so similar, why do you have them both?

Regards,
Brian.

Brian,

Thank you for all your efforts. This is going to work for now. I'm thrilled!!! As far as having the two version installed, I just wan't sure about the stability of Excel 2010 when I installed it so I chose the option to not replace. I have had some issues with Access 2010 that seem to work with Access 2007. That was a while back, though, so maybe service packs have fixed that issue.

I'm going to accept your solution and give you the highest rating possible.

Thanks again just one more time!!!

Best regards,

Phil

Hello Brian,

I can't thank you enough for your efforts! It works perfectly. I can't wait to try it out on Monday.

I wish you the very best in all pursuits.

Best regards,

Phil

Absolutely perfect !!!

Thanks, Phil! I'll keep an eye on this for the next few days.