Solved

Excel Alert Not Working

Posted on 2011-02-16
55
291 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:cpatte7372
  • 29
  • 13
  • 13
55 Comments
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 34907422
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 !
0
 

Author Comment

by:cpatte7372
ID: 34908003
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
0
 

Author Comment

by:cpatte7372
ID: 34908066
Fernando,

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

Cheers
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 34908068
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,
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 34908145
well, this data/validation does not say the row number, but it's pretty accurate...
please verify.
Copy-of-Trade-Day--100-Template.xlsm
0
 

Author Comment

by:cpatte7372
ID: 34908782
OK, I'm back.

Going to check it out.

0
 

Author Comment

by:cpatte7372
ID: 34908819
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.

0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 34908846
so have fun ! :-)
0
 

Author Comment

by:cpatte7372
ID: 34908933
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
0
 

Author Comment

by:cpatte7372
ID: 34908958
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 :-)
0
 

Author Comment

by:cpatte7372
ID: 34909094
Fernando,

Just had one of my stocks fall below 0.02% and I didn't get an alert :-(
0
 
LVL 6

Assisted Solution

by:FernandoFernandes
FernandoFernandes earned 250 total points
ID: 34909100
you will have to see the validation formula...
Excel has a very unique way of using formulas in the validation and in the conditional formats...
The dollars in the formulas and where they're being applied determine it all ...

I selected the whole row number 2...
Then, as used to Excel 2003, Alt+D then L
this opens the Validation screen...
I selected Type: Custom and wrote a formula based on column 15 (I read 15 on the VBA code) so, it's column "O" (the letter).

the formula is to determine what values are ALLOWED in a specific cell...
So I validated that for all the cells in row number 2, they can only be changed if the final result of column 15 in the same row is bigger than or equal to 0.02%...

After that, I copied the whole row, and selected the rest of the sheet, and pasted special, validations only ...

since I used the dollar sign in the column only, not on the row, the validation automatically updated the row in the formula for every row in the table...

I'm sorry, am I making sense ? Sometimes I am not good at explaining stuff...

Hope it's more clear now ....
0
 

Author Comment

by:cpatte7372
ID: 34909101
And another, still no alert .....
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 34909125
tell me please which cells you changed and what was the value... so I can test here !
0
 

Author Comment

by:cpatte7372
ID: 34909257
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?
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 34909310
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 ?
0
 

Author Comment

by:cpatte7372
ID: 34909366
yes please mate...
0
 

Author Comment

by:cpatte7372
ID: 34909721
Fernando,

You still trying to help me out?
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 34909747
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 !
0
 

Author Comment

by:cpatte7372
ID: 34909781
Thanks ever-so-much.

0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 34909933
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
0
 

Author Comment

by:cpatte7372
ID: 34910079
Fernando,

Checking it out now...
0
 

Author Comment

by:cpatte7372
ID: 34910136
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
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 34910268
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 ?
0
 

Author Comment

by:cpatte7372
ID: 34910468
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 ......
0
 

Author Comment

by:cpatte7372
ID: 34911940
Hi Fernando,

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

Cheers

0
 

Author Comment

by:cpatte7372
ID: 34912055
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 34912072
I'm on my wait home now, will look at it later tonight !
0
 

Author Comment

by:cpatte7372
ID: 34912087
Thanks man.

BTW, What country are you from?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34912300
cpatte7372,

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

Regards,
Brian.
0
 

Author Comment

by:cpatte7372
ID: 34912350
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34912462
cpatte7372,

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

Thanks,
Brian.
0
 

Author Comment

by:cpatte7372
ID: 34912503
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34912622
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.


0
 

Author Comment

by:cpatte7372
ID: 34912666
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34912700
cpatte7372,

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

Thanks,
Brian.
0
 

Author Comment

by:cpatte7372
ID: 34912725
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.....
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34912931
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.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34913058
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.
0
 

Author Comment

by:cpatte7372
ID: 34914614
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34914993
cpatte7372,

Thanks. What's your current time?

Regards,
Brian.
0
 

Author Comment

by:cpatte7372
ID: 34915117
My current time here in London, is 11:44am

0
 
LVL 26

Expert Comment

by:redmondb
ID: 34915354
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.
0
 
LVL 26

Accepted Solution

by:
redmondb earned 250 total points
ID: 34915858
cpatte7372,

OK, lots of mucking about with Word. I've no idea whether the results are transferable to your external applications, but without a copy of it and a stock market to play with, it's all I can do.

Using the range D2 to D101 (but writing each cell individually)...
(1) I output 1200 (forcing lots of < 0.02%) to each cell, slept for 5 seconds and then re-wrote the cells with values resulting in all Column O cells > 0.02%. The result was that as the first lot were written, O1 went red. As the second lot were written it went back to yellow. I then checked the cells in D2 to D101 and they were all correct. (The only funny was that the warning beep from the initial values happened a few seconds after the run had finished.)
(2) I repeated (1) with different delays (3 seconds, third of a second, 50 milliseconds, etc.). The results were the same.

So, updates weren't lost and I wasn't driven demented by constant beeps. (However, if you're getting frequent stock updates and there's even one <0.02% value on the sheet then, whether it's been updated or not, you'll be continually beeped - drop the beep, you'll still have the red header.)

I've posted the code below, in case I'm not around at the time.

Final warning: I'd strongly recommend that you check out with the external applications technical support, as mentioned earlier. I've no idea whether or not the code may intermittently interfere with its updates. I'll be deeply regretful if this macro causes you problems, but it's your responsibility, not mine.


Regards,
Brian.
0
 

Author Comment

by:cpatte7372
ID: 34916363
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 :-)
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 34916435
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...
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34916649
Thanks, FernandoFernandes.
0
 

Author Comment

by:cpatte7372
ID: 34916965
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
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23367661.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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34917130
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.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34917347
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.
0
 

Author Comment

by:cpatte7372
ID: 34917397
Brian,

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

Cheers mate.
0
 

Author Comment

by:cpatte7372
ID: 34917419
Brian

Thanks.

Hopefully, Fernando has been tracking this question

Thanks again mate...
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 34917431
I am ...
0
 

Author Closing Comment

by:cpatte7372
ID: 34927535
Brian / Fernanado you guys make my subscription worth every penny
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34927789
Thanks, cpatte7372. Take care!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now