Link to home
Get AccessLog in
Avatar of cpatte7372
cpatte7372Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Excel Alert Not Working

Hi Experts,

In related post, an expert, (Brian) helped with a formula that will alert when a certain level is reached, however the alert only seems to work if the number is manually changed rather than automatically.

I have asked Brian if he could take another look at it, however if there are any experts that could help out in the meantime that would be great.

If you're unable to refer back to the related question please let me know.

Cheers

Carlton
Avatar of FernandoFernandes
FernandoFernandes
Flag of United States of America image

well, should we guess that the alert is being defined in the data/validation, correct?
this is how excel works, if you change one cell, the validation in that cell may allow (or not) the number to be put there, with some alerts.
BUT if you have validation in a cell that has a formula, then you change content of another cell, it will automatically get updated by Excel's calculation, and it seems like the validation of this cell is never "triggered"...

to work around this issue (if this is what you're talking about) you have to make a custom validation and usa a formula in the validation, defining that a certain other cell cannot have a value out of your range, in case the other cells are changed...

examplifying:
cell A1 has a fórmula:
=B1

put validation in A1 determining it can be any number between 0 and 10.
See, by changing the content of B1 to 15, cell A! will reflect 15 as if no validation was there.

Reason: you're indeed NOT changing the content of A1.
Instead, you would have to determine validation on B1, as Custom with this formula:
=and($A$1>=0,$A$1<=10)

cheers !
Avatar of cpatte7372

ASKER

Fernando,

Thanks for responding.

My Excel programming is somewhat limited. I have attached a copy of my day trading spreadsheet.

Would you be kind enough to show me where you would include the additional formula.

Cheers mate..


Copy-of-Trade-Day--100-Template.xlsm
Fernando,

If you do help, just letting you know Ill be back in 20mins

Cheers
Sure, let me ask you one thing then

1) I see the VBA code in the sheet, I'll have it removed and replaced with validation...
2) That code is checking for every single cell that was changed, if the respective value on column I is less than 0.0002 (or 0.02%). In this case, it should a) allow the change to be made but b) show a warning message box, is this the expected behavior ? I just need to know, because I want to be the more accurate possible.

Thanks,
well, this data/validation does not say the row number, but it's pretty accurate...
please verify.
Copy-of-Trade-Day--100-Template.xlsm
OK, I'm back.

Going to check it out.

Fernando,

I'm in the process of verifying. However, in response your question, " In this case, it should a) allow the change to be made but b) show a warning message box, is this the expected behavior ? "

Yes, this is the expected behaviour.

so have fun ! :-)
Fernando,

Thanks dude, but before you go can you let me know how to accomplished it? I don't understand how you got it to work with validation.

Cheers
Also, although it appears to be working, the real test is when a stock that I'm hoping to buy/sell falls below 0.02%.

However, I have every faith in your work :-)
Fernando,

Just had one of my stocks fall below 0.02% and I didn't get an alert :-(
SOLUTION
Avatar of FernandoFernandes
FernandoFernandes
Flag of United States of America image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
And another, still no alert .....
tell me please which cells you changed and what was the value... so I can test here !
Fernando,

The numbers in col D are moving stock prices. These prices change throughout the day as the market fluctuates. So those numbers are provided by a broker throughout the day. I don't change those numbers - basic supply and demand.

Does that explain?
you don't change them ?
OMG.... you never mentioned it...
you said they're changing automatically, but I understood they were changing because you were typing on different cells....
well.... damn....
in this case there's a big chance validation will not solve your issue, because validation only triggers when a cell is manually changed...
i think we'll have to go back to the VBA approach, and trigger the VBA validation in the calculate event...

wanna do it ?
yes please mate...
Fernando,

You still trying to help me out?
yes I am ... I just also had some thing to do here at work....
but give me an hour and I'll post the good VBA code within the file I already have !
Thanks ever-so-much.

see if this attends your needs...

just one thing...
0.00% is less than 0.02%
the other values that will show at first, the are negative, therefore, less than 0.02%...

if you want me to get the absolute number then anything between -0.02% and 0.02% will be in the warning...

let me know ...
Copy-of-Trade-Day--100-Template.xlsm
Fernando,

Checking it out now...
Fernando,

I just need anything =< 0.02%, and only when an individual cell =0.02% or below.

At the moment, it's alerting on everything below 0.

Also, I can't get rid of the message box when I click 'ok'

Cheers
anything below 0 is below 0.02...
not getting rid of the message box ?
DAMN that must mean that ... oh no...

how often are values being updated? if the calculate event is being triggered every second, you'll keep seeing the message box forever...

then I'd have to think of something else to stop it from running so often, maybe once a minute, or once every 5 minutes ?
Fernando,

The values are updated everytime a stock price changes which can sometimes be in seconds.

The way it was originally written by one of you other expert colleagues, was perfect - it just didn't alert when the values changed automatically.

Also, I'm being a bit thick - I do recognise anything below 0 is below 0.02.

Is it not possible to have an alert once and start again when the value goes above 0.02. I know that's bad English, but I think you know what I mean ......
Hi Fernando,

Just wondering if you were still prepared to help me with this issue?

Cheers

Hi Fernando,

Just thought I would let you know that the message alert box in the attachment above 'Copy-of-Trade-Day--100-Template.xlsm' is popping up even when nothing is changing.

Hope you're still around to help.
I'm on my wait home now, will look at it later tonight !
Thanks man.

BTW, What country are you from?
Avatar of redmondb
cpatte7372,

Fair enough, the market sets the price, but how exactly are these changes being made to the spreadsheet?

Regards,
Brian.
Hi Brian,

I have signed up with a broker that makes an online connection with a program on my desktop from which I import the data into the spreadsheet.
So when the price changes its reflected in the spreadsheet
cpatte7372,

Excellent! How do you import the data? (So we know what to detect.)

Thanks,
Brian.
Its hard to describe. Basically, it's like any online market data feed. The data is fed into a program from a brokerage firm and then there is a form DDe link between the program and excel
cpatte7372,

... driven by the external program, yes?

Let's see if we can trap it. Please insert the following code into the "TRADING" sheet's code (i.e. not a normal module).

Private Sub Worksheet_Calculate()

Debug.Print "Hello"

End Sub

Few things...
(1) This is just to check we can "see" the external application's activities.
(2) Every time the sheet recalculates, "Hello" will be displayed in the Immediate window. (Can you show that - go into the VBE, select "View" on the menu bar and click on "Immediate Window").
(3) To make sure you're really seeing the external program...
   - (A) Stop the external application.
   - (B) Clear any "Hello's" from the Immediate Window.
   - (C) For the moment, don't touch anything in excel!
   - (D) Start the external application.
   - (E) Wait until you're absolutely sure there's been an update fro the external application.
  - (F) Go back into the VBE window.
  - (G) If all goes well, there should be a load of "Hello's"

IF everything is OK, then we're home free. All that has to be done is take the previous code I gave you and fire it on Worksheet_Calculate.

Regards,
Brian.


Unfortunately, because the markets are closed and it's 1:30am we won't be getting any market data and hence no change in prices.

I will carry out the actions you suggested in the morning.

In the meantime, I will be awake for another 30mins if you have any further suggestions.

Cheers
cpatte7372,

If this goes OK, there's little more to be done. How many hours 'till you can test this?

Thanks,
Brian.
Well the stock markets open 9:30am EST. It is only then that we can truly test. If you remember, last night you and I thought we nailed it but it was only when I started trading that I realised it wasn't working the way it should.....
cpatte7372,

Einstein rears his woolly head again. Please give me a relative time - how many hours from your reply?

Yes, the answer you accepted from the other expert was faulty. Yes, my "catch" was provided after the question was closed, was correct for any manual changes, was tested and passed by you and was provided long before there was any mention of an external application!

Hence the Debug. :)

Regards,
Brian.
jppinto,

Sorry, just saw the delay between your last message and mine, so you won't get this until the morning, your time. Assuming EST is American, I'll be up long before you, so please send me your time as soon as you read this. I'll make sure to be around at "9:30am EST".

Oops, just had a horrible thought about your external application. Is it possible that updates may be delayed or even lost while the MsgBox is displayed?

To be sure, to be sure, what about something like the following...
 - Drop the MsgBox.
 - When a Column O value below 0.02% is found then Beep and change Column O's heading colour from Yellow to Red. (Perhaps even the entire header row.)

Do you have access to the external application's technical support to ask them a few questions?
 - Could an open MsgBox cause an update delay?
 - Could a running macro cause an update delay?
 - Can an update delay turn into lost updates (or even a crash of the external application)?

Thanks,
Brian.
Hi Redmond,

I look forward to speaking with you around 9:30 American EST.

Just so you know I would be very happy to drop the message box. A beep would be fine. So where you say 'When a Column O value below 0.02% is found then Beep and change Column O's heading colour from Yellow to Red', that would be great.

Speak soon

Thanks
cpatte7372,

Thanks. What's your current time?

Regards,
Brian.
My current time here in London, is 11:44am

cpatte7372,

Thanks. New York 5 hours behind?

I've made quite a few changes and as a final test I'm about to try to simulate your external application (using Word).

The macro now...
 - Checks there's at least one row of data. Silently terminate if not.
 - For every cell in Column O down to the last row, if any non-error, non-blank cell is less than 0.02% then beep, change O1 to red, exit.
 - If all non-error, non-blank cells are >= 0.02% then change O1 to yellow.

Regards,
Brian.
ASKER CERTIFIED SOLUTION
Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Hi Brian,

I am writing this from my iPad. I will be at my desktop in 10mins.

What you've done looks quite impressive. I can't wait to check it out :-)
hey gentlemen, hey cpatte7372, ... sorry I had a situation last night and couldn't get back online...
cpatte7372, I'm from Brazil, but I live in NYC for the past three years working exclusively with Excel and VBA (which I already had a lot of experience before I was selected for the job)...

Brian is providing good ideas and answers... I wanna see how and where it's going, because I'm also interested in the final solution for your case... I unfortunately ran out of ideas...
Thanks, FernandoFernandes.
Hi Brian,

I've been checking it out. I'm not losing any updates which is great, however because stock prices are constantly changing I'm continually getting the beeping - which would drive me crazy.

Are you suggesting there is no way of getting an alert to beep once when a condition is met and beep only when the condition has re-occured?

For example, 0.02% had been reached, then beep. If the stock then goes above 0.02% and then falls below it, beep again.

Is that possible?

I have been poking around on this web-site to see if anyone else has made a similar request and I came up with
https://www.experts-exchange.com/questions/23367661/Create-Conditional-Desktop-Alert-from-Excel.html?sfQueryTermInfo=1+10+30+alert+excel

01/05/08 06:18 AM, ID: 23367661


Do you think we could gleen any information from that request.

Really appreciate your help guys

I've attached a sample with the code inserted.

Cheers
Trade-Day--10-TemplateV2.xlsm
cpatte7372,

It's not a problem - I was actually doing it that way, but I was concerned that the extra processing might be pushing things as far as the external application was concerned. Without knowing your working conditions, frequency of updates, frequency of "<0.02%" items, duration of "<0.02%" items, etc. I left well enough alone. For the moment, you have my suggestion about turning the beep off.

Cheers,
Brian.
cpatte7372,

Without understanding what your external application's requirements and tolerances are, I'm already uncomfortable with my solution. If you can get no information from its provider, then I'm not willing to make any further changes.

You have a working solution, of sorts. FernandoFernandes is more than competent to make the changes required to beep only on change, so I'm bowing out now.

Best wishes with this,
Brian.
Brian,

Thanks. I've turned off the beep. In the meantime, I'll await for the results of your previous suggestion.

Cheers mate.
Brian

Thanks.

Hopefully, Fernando has been tracking this question

Thanks again mate...
Brian / Fernanado you guys make my subscription worth every penny
Thanks, cpatte7372. Take care!