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

 

[Valentino]

Only "0" to "1", "1" to "0" is not necessary.

Does this then show all changes cumulatively, eg which happened in last hour (6 refreshes) if i didnt check/close the pop up? Or does it give a pop up every 10 minutes with new changes? First would be preferred, one box showing all changes since last check...

 

[johnnyL]

The pop up box will essentially pause the excel program from running, so if you leave the pop up message for 2 hrs, your script will not be running during that time. That is the problem with a pop up message. You could save the results from every 10 minute refresh to a sheet that you can refer back to later if you want a history. That will not give you the pop up alert however. I mentioned an audible alert ... this would not pause the script waiting for you to respond to it, but you have to have speakers on your computer and be close enough to hear it if you were depending on that type of alert.

 

[johnnyL]

Only "0" to "1", "1" to "0" is not necessary.

If that is the case, then can that column be reset to all zeroes after every 10 minute refresh?

 

[johnnyL]

I will also need to see the the code that does the 10 minute update also.
You can alter any sensitive info with xxx if you need to. But I will need to see how it is set up to incorporate the changes that you desire.

 

[johnnyL]

Actually disregard my last post, post #14. I can do what I am thinking of doing another way.

 

[johnnyL]

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"

After looking into this a bit more, I will need to see the formulas you have in the first few rows of column E. I also need to ask if those other 3 values that you mentioned are just values, or are they a result of formulas also. It sounds like the 3 values do not involve any formulas, but I have to ask.

 

[Valentino]

After looking into this a bit more, I will need to see the formulas you have in the first few rows of column E. I also need to ask if those other 3 values that you mentioned are just values, or are they a result of formulas also. It sounds like the 3 values do not involve any formulas, but I have to ask.

Indeed Johnny, its just values refreshed from data query, and inputs for if statement in colmn e

 

[Valentino]

The pop up box will essentially pause the excel program from running, so if you leave the pop up message for 2 hrs, your script will not be running during that time. That is the problem with a pop up message. You could save the results from every 10 minute refresh to a sheet that you can refer back to later if you want a history. That will not give you the pop up alert however. I mentioned an audible alert ... this would not pause the script waiting for you to respond to it, but you have to have speakers on your computer and be close enough to hear it if you were depending on that type of alert.

Was also just thinking of it, maybe the macro can save the changed rows on a separate sheet. Then instead of a pop up i just look at that sheet from time to time, but at least i know where to look to see if there are changes. Would the script then not pause and always be on?

Thanks so much for looking into this, if you could give me the code i can test and play with it to refine

 

[johnnyL]

Indeed Johnny, its just values refreshed from data query, and inputs for if statement in colmn e

Which columns are the data query values stored in? Are all 3 of those columns refreshed every 10 minutes?
You didn't post a few of the if formulas like I asked for.
You mentioned 100 rows, is that rows 1 to 100?

 

[Valentino]

Which columns are the data query values stored in? Are all 3 of those columns refreshed every 10 minutes?
You didn't post a few of the if formulas like I asked for.
You mentioned 100 rows, is that rows 1 to 100?

Johnny,

please find attached a picture of the workbook including the condition formula, i had issues downloading XL2BB due to restrictions from my employer on my laptop. The grey shaded areas are values refreshed from web query, and in column E you see the condition.

as i come to see now, column E cal also have "-1"; so what would be needed is a macro to write all rownumbers that changed from "0" to either "-1" or "1" in a separate sheet. The macro would only need to do so when the workbook is open, not required to come up with changes that have occured before the workbook was open (eg during the night or previous day)

Hope this clarifies! Thanks so much