Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

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
0
cpatte7372
Asked:
cpatte7372
  • 29
  • 13
  • 13
2 Solutions
 
FernandoFernandesCommented:
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
 
cpatte7372Author Commented:
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
 
cpatte7372Author Commented:
Fernando,

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

Cheers
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
FernandoFernandesCommented:
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
 
FernandoFernandesCommented:
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
 
cpatte7372Author Commented:
OK, I'm back.

Going to check it out.

0
 
cpatte7372Author Commented:
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
 
FernandoFernandesCommented:
so have fun ! :-)
0
 
cpatte7372Author Commented:
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
 
cpatte7372Author Commented:
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
 
cpatte7372Author Commented:
Fernando,

Just had one of my stocks fall below 0.02% and I didn't get an alert :-(
0
 
FernandoFernandesCommented:
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
 
cpatte7372Author Commented:
And another, still no alert .....
0
 
FernandoFernandesCommented:
tell me please which cells you changed and what was the value... so I can test here !
0
 
cpatte7372Author Commented:
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
 
FernandoFernandesCommented:
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
 
cpatte7372Author Commented:
yes please mate...
0
 
cpatte7372Author Commented:
Fernando,

You still trying to help me out?
0
 
FernandoFernandesCommented:
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
 
cpatte7372Author Commented:
Thanks ever-so-much.

0
 
FernandoFernandesCommented:
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
 
cpatte7372Author Commented:
Fernando,

Checking it out now...
0
 
cpatte7372Author Commented:
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
 
FernandoFernandesCommented:
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
 
cpatte7372Author Commented:
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
 
cpatte7372Author Commented:
Hi Fernando,

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

Cheers

0
 
cpatte7372Author Commented:
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
 
FernandoFernandesCommented:
I'm on my wait home now, will look at it later tonight !
0
 
cpatte7372Author Commented:
Thanks man.

BTW, What country are you from?
0
 
redmondbCommented:
cpatte7372,

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

Regards,
Brian.
0
 
cpatte7372Author Commented:
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
 
redmondbCommented:
cpatte7372,

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

Thanks,
Brian.
0
 
cpatte7372Author Commented:
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
 
redmondbCommented:
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
 
cpatte7372Author Commented:
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
 
redmondbCommented:
cpatte7372,

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

Thanks,
Brian.
0
 
cpatte7372Author Commented:
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
 
redmondbCommented:
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
 
redmondbCommented:
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
 
cpatte7372Author Commented:
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
 
redmondbCommented:
cpatte7372,

Thanks. What's your current time?

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

0
 
redmondbCommented:
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
 
redmondbCommented:
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
 
cpatte7372Author Commented:
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
 
FernandoFernandesCommented:
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
 
redmondbCommented:
Thanks, FernandoFernandes.
0
 
cpatte7372Author Commented:
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
 
redmondbCommented:
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
 
redmondbCommented:
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
 
cpatte7372Author Commented:
Brian,

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

Cheers mate.
0
 
cpatte7372Author Commented:
Brian

Thanks.

Hopefully, Fernando has been tracking this question

Thanks again mate...
0
 
FernandoFernandesCommented:
I am ...
0
 
cpatte7372Author Commented:
Brian / Fernanado you guys make my subscription worth every penny
0
 
redmondbCommented:
Thanks, cpatte7372. Take care!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 29
  • 13
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now