• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Preventing popup from showing repeatedly

vipuls

New Member
I have an excel file which refreshes in real time (nearly every 1 second). A function is called as an excel formula which throws a popup on a particular condition being met. My interest lies in identifying only when a condition is met for the first time. Once condition is met, I do not want function to show popup repeatedly. However, because excel is refreshing fast in real time, the function condition keeps getting met repeatedly and hence popup comes again and again after pressing Ok. Is there a way to avoid this? I am thinking of passing a flag (excel cell value) as well as an input to the function. Is there a way to change the flag (excel cell value) from within function? Or any other solution without making calculation manual?
 

p45cal

Well-Known Member
interest lies in identifying only when a condition is met for the first time
…so when does a reset occur? (So that it pops up again)
Is there a way to change the flag (excel cell value) from within function?
A function called from a worksheet formula can only change the value in the cell(s) the formula is in; it cannot change the value in other cells.
A function called from a Sub (another macro) can.
(And you can't get round it by calling a function from another function either!)
What causes Excel to refresh?
Event handlers can be used to change values in cells on sheets, but often, RTD doesn't cause the worksheet_change event to fire.
However, there is a _Calculate event that can be triggered every time a sheet is recalculated (which is usually after a value on the sheet changes which has a dependent cell elsewhere), and this could write values to cells.
A variable can also be declared using the word Static which remembers the last value it had when the sub it's in was last called. This is often used in such cases.
Need more specifics, better, a workbook and some existing code.
 

vipuls

New Member
Thanks a lot p45cal. Was able to achieve this through a macro triggered to run every second (using Application.OnTime). After getting the popup, was able to change flag value so that popup did not come again (unless flag was again changed manually)
 

vipuls

New Member
Only one minor concern. Can Application.Ontime slow down the excel. Will test over the next few days. Thanks once again.
 
Top