I have a problem.

I write to cell, and I want to wait until ALL code that fires off that event
to finish before I run my code to read the resulting cells.

I tried a DoEvents and that worked in most 99.9% of the cases and has for
years. BUT with the last save the timing got messed up and I had to put in
a 3-5 second delay in this one sheet (looping until timer OldTimer + 5)

Any better way of doing this?

(This was my single step problem, not working it 1 of 2 methods of full
speed operation and when I single stepped it.)

PS.
Excel 2000

Let me get it clear first.
You have a procedure (Procedure1) in which you write to the cell. Then
you have another procedure (Procedure2) fired by the change in the cell
(may be worksheet_chage).
Right?

So what you do is.
1. If there is no module in your vba project insert a module.
2. At module level declare a public vairable as under -
Public canInow as Boolean
Declare this public vairable out side any procedure. (The top line,
before any Sub.. should be above declaration line.)

3. In Procedure1, just before you write to cell, enter following:-
canInow = False
'your code to write to cell
4. After your code to write to cell, you wait for canInow to become
True. The enitre code will look like :-
canInow = False
'your code to write to cell
10
If Not canInow Then
DoEvents
GoTo 10
End If
'your rest of the code.

5. Now in Procedure2, the last line before End Sub Should be:-
canInow = True

Sharad




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Yes, Worksheet_change calls data sheet sub's. which may call itself 2 times.

All this code is in modules.

Thanks, This gives me ideas, but I can modify in the place you suggest maybe
I will do that in the cell change event.

Does this works even if the displayed results on the screen is slow due to
video speed.




"Sharad" wrote in message
...
Let me get it clear first.
You have a procedure (Procedure1) in which you write to the cell. Then
you have another procedure (Procedure2) fired by the change in the cell
(may be worksheet_chage).
Right?

So what you do is.
1. If there is no module in your vba project insert a module.
2. At module level declare a public vairable as under -
Public canInow as Boolean
Declare this public vairable out side any procedure. (The top line,
before any Sub.. should be above declaration line.)

3. In Procedure1, just before you write to cell, enter following:-
canInow = False
'your code to write to cell
4. After your code to write to cell, you wait for canInow to become
True. The enitre code will look like :-
canInow = False
'your code to write to cell
10
If Not canInow Then
DoEvents
GoTo 10
End If
'your rest of the code.

5. Now in Procedure2, the last line before End Sub Should be:-
canInow = True

Sharad




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Does this works even if the displayed results on the screen is slow
due to
video speed

Yes, because it has nothing to do with processor or vedio speed.

which may call itself 2 times.
Now this could be the problem. Do you mean that the after you write
data to the cell, when it fires the event, it may call that event
(worksheet_change) 2 times before you want to procede further?
If yes, are you sure that it will always call 2 times, or say awlays a
'n' number of times?

Or atleast do you know te address of the last cell that will change,
before you want to procede? If yes, then you can set 'canInow = True' in
the event of the last cell that you know will change.
But it that cell does not change, you will land up in an infinite loop,
excel will just hang, unless you press 'Ctrl+Break'.

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

am just relaying what one engineer said as to how it worked. They are not
CS guys and I am not sure he knew what he was talking about. I think he
meant 2 of the ~80 values get done 2 times if certain conditions are met.
If that is true then it would stay inside the routine that is called from
the sheet_change event.

No I can't assume a last cell. They knew nothing of the benefits of a real
table.

I found one thing that seemed to make a difference. I had not set the
sheet that I takes/displays the data the active sheet. After I did that
thru code, I could also not get it to fail.

I have given them 2 choices, a delay loop around a DoEvents, or use their
global variable that they already had (I choose not to use it originally).


"Sharad" wrote in message
...
Does this works even if the displayed results on the screen is slow
due to
video speed

Yes, because it has nothing to do with processor or vedio speed.

which may call itself 2 times.
Now this could be the problem. Do you mean that the after you write
data to the cell, when it fires the event, it may call that event
(worksheet_change) 2 times before you want to procede further?
If yes, are you sure that it will always call 2 times, or say awlays a
'n' number of times?

Or atleast do you know te address of the last cell that will change,
before you want to procede? If yes, then you can set 'canInow = True' in
the event of the last cell that you know will change.
But it that cell does not change, you will land up in an infinite loop,
excel will just hang, unless you press 'Ctrl+Break'.

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!