We help IT Professionals succeed at work.

Microsoft Excel Private Sub Worksheet_Change(ByVal Target As Range)

Phil
Phil asked
on
I'm trying to set up a spreadsheet and capture a change in the (Contents or calculated Value) of a cell. One of the cell's formulas is as follows:=IF(AND(B11<=D11,B11>0),"BUY","").
My problem is that I want to have an audible beep sound when I get  "BUY" signal. The change event only fires when the cell value is changed by keyboard input, not if it changes via formula.I can't reference Column B or D with the change event as those numbers are changing constantly.

Thanks so much!

Phil Brann
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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.
PhilRetired

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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.
PhilRetired

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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
PhilRetired

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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.

PhilRetired

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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.
PhilRetired

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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
PhilRetired

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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.


CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT

Commented:
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
PhilRetired

Author

Commented:
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
PhilRetired

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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.

CERTIFIED EXPERT

Commented:
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.
PhilRetired

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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

Open 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.
PhilRetired

Author

Commented:
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
CERTIFIED EXPERT
Commented:
Phil,

Whoa! Thanks for the kind words, but I don't think the last version is doing what you want. This version solely looks at columns G:I. It remembers their previous values and only beeps if a value goes from blank to one of your flags. Please check it out! (I've also removed the messages.)

Regards,
Brian.
Trade-20111104-Sample.-V5.xlsm
PhilRetired

Author

Commented:
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
PhilRetired

Author

Commented:
Absolutely perfect !!!
CERTIFIED EXPERT

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