pausing macro while cells update

[Spike]

I have a spreadsheet with some Reutes Wealth Manager formulas. I wish to
change the RIC code in some of these cells and then check if the code is
valid. For this i need to pause the macro while the cell/s update.

If i use Application.wait then everything pauses and the cells do not
update. I guess i need to find out what the code is to refresh the Reuters
links

Any ideas will be gratefully recieved

 

[JLGWhiz]

You can insert this snippet in where you need to pause. It will operate on
tenths of seconds as well as whole seconds. i.e. 1 for whole, 0.1 for
tenths.

s = Timer + 0.5
Do While Timer < s
DoEvents
Loop

The DoEvents allows other activity to process during the macro pause.

 

[Gary''s Student]

Here is a typical method:

Sub WaitABit()
' do some stuff
MsgBox ("Do your thing and set Z1 when you are done")
While Range("Z1").Value = ""
DoEvents
Wend
MsgBox ("Thanks! I will proceed")
End Sub

1. we inform the User they must take some action
2. the code just loops until Z1 has been set, but the the sheet has focus to
allow User activity
3. when the User finally set Z1 to something, the code proceeds.

 

[Spike]

Thanks for that.

If i use a msgbox then it is fine as the macro stops and the cells update. i
am trying to avod using msgboxes as that requires User intervention to clcik
the msgbox.

 

[Gary''s Student]

Then have your macro first put up a TextBox telling the User what to do and
then after the pause, remove the TextBox.


You just need to put something up so the User is given direction.

 

[Spike]

Thank you both for that, i will try on monday as do not have the connection
to Reuters here

 

[Spike]

Thank you the Do while Timer loop worked fine with a two second delay.

Very much appreciated