Pause Excel Until Condition is Met

[kclong]

How do I write a code that will pause excel until a condition is met, say, the value in cell A1 = 100, for example. The formula in A1 is a sum function. My spreadsheet is linked to data outside of excel and sometimes it takes time before the data populates. I can't use a time delay because the time it takes to populate varies, and sometimes does not populate. An input box won't help. And, I don't want the macro to run if the condition is met, and run a different function if the condition is not met. I just want it to pause and wait until the condition is met before proceeding. Is this possible? Any help you can give is great.

 

[medexcel]

BUMP - as I have a similar problem.

I have a file with a number of worksheets as follows:

FORM - identical to a paper data entry form, people have to enter data into certain boxes.

DATA - where the data is kept subsequently. Row 1 is linked by formulae to the data boxes in FORM, row 2 is the headings for the data columns, subsequent rows are the data themselves.

and various other worksheets which do analyses on the data that is stored in the worksheet DATA but therefore slow down the file considerably. (As the users don't know how to transfer data out of one Excel file into another, nor how to use Access, I've had to do it this way.)

Now, after filling in all the data in the worksheet FORM, the user clicks on a macro which copies all the data from row 1 of DATA, finds the first blank row below that, copies the values into it, then goes back to the first worksheet FORM and deletes each box, wiping the data entry form and making it ready for the next data entry.

The problem is, there are a number of quite complex calculations involved and sometimes when the row in DATA is copy/pasted, not all the values have been calculated before the user runs the macro which pastes the values and so some fields are pasted blank (I think that's what is happening). Of course, if the user simply looked to see the word "READY" appear in the status bar of the FORM worksheet and waited until it was there before running the macro and pasting the data, this wouldn't be an issue.

So my own question is, is there any way of telling Excel to run a macro ONLY AFTER "READY" has appeared in the status bar (if it's not already appeared) so that all the formulae have ben given the chance to update themselves before the macro continues? I think that might just achieve what kclong wants also....

 

[medexcel]

Quick update to this - so I don't suppose anyone has an idea how Excel can "know" that it's finished calculating, so that when my users click on my "activate macro" button, the macro doesn't start doing its stuff until it's checked that any existing calculations are all completed and status bar says READY?

 

[Ruddles]

Wait until A1 = 100:-

Do Until Range("A1").Value = 100
  DoEvents
Loop

Wait for Excel to finish calculating:-

Do Until Application.CalculationState = xlDone
  DoEvents
Loop