Excel alerts

[Valentino]

Hi everyone,

In an excel file i am refreshing data from a web query every 10 minutes. In the sheet i have some if formulas which check whether certain conditions have been met. If these are met, i would like to get a notification or alert, not necessarily by email but just in excel as i have the file open all day but just not scanning it all the time. Is there an easy way to do this, or can it only be done by vba? Is there some example of this solution/vba script?

Many thanks for your thoughts!!!

Valentino

 

[johnnyL]

I am fairly certain it would require vba code, probably to scan for a change that might be made on the sheet when your conditions are met? You would need to supply more info about what happens when your conditions are met.

 

[Valentino]

Thanks Johnny for thinking along!
The sheet is fairly simple, it has ca 100 rows/assets each with 3 values updated from web query. In a column to the right i have an if statement, testing for certain conditions being met: if so, it shows "1" (others not meeting conditions are "0"). I would like to get notified in some way if one of the assets changed from "0" into "1"

 

[johnnyL]

What sheet name / address range will change from '0' to '1', and what type of alert are you wanting? An audible alert, pop up box, or what?

 

[Valentino]

Johnny,

So column e has the if statement formula, vertically down for 100 assets, its all on same sheet.

Have not really been thinking about type of alert, was just wondering whether it would be possible..but what would be really cool, if for instance cell e59 goes from "0" to "1" it would show "59" in the pop up box! Would this be possible and how would this work if multiple rows change from "0" to "1" (and since the sheet updates every 10 minutes, maybe 10 minutes after each other)? Could such a macro then show ALL row numbers which have changed since i last checked?

Thanks

 

[johnnyL]

Baby steps. Give me a few because I am working on something else right now, but I will post back here when I am done.

 

[Valentino]

Thanks mate! No rush at all, will only be able to check later tomorrow cause now not behind screen. Very curious though to see how this will work thanks a lot, learning some good stuff here

 

[johnnyL]

One more question I thought of, do some of the '1's get changed back to '0's at the 10 minute update?

 

[Valentino]

Very good question, in fact it might be that they change back to from "1" to "0" at an update...but if possible, i would then still like to see the alert for that row, so that i can check what happened. So the pop up should show all row numbers which (since last check) changed from "0" to "1", irrespective of current value (as indeed they may have changed back from "1" to "0"). Would that be possible...? And what would it take as starting point: eg if you clicked "Ok" on the pop up it "resets" and next time shows all changes which happened since then..?

 

[johnnyL]

So you want a pop up box showing all changes? 1 to 0 & 0 to 1?
The changes @ the next ten minute 'refresh' would not trigger unless you have closed the pop up message.