wait until cell calculations have finished

Posted by Geoff CE on September 04, 2001 1:30 PM

I have a combobox macro that depending on the users choice changes the values of certain cells, eg if choice 1 selected, A1="ON". The macro then jumps to a separate subroutine

Is there anyway of getting this subroutine to wait until the calculations in certain spreadsheet cells have updated, eg cell A2=A1*2, before executing.

I understand I can use a wait command for a particular length of time, but this seems rather messy.

Posted by Dax on September 05, 2001 2:35 AM

Excel provides a Worksheet_Calculate event that is fired each time a worksheet is calculated. Try this for an example. Right click on a worksheet tab and select View Code. Try this:-

Private Sub Worksheet_Calculate()
MsgBox "Calculation complete!"
End Sub

You could utilise this to ensure your code runs only once a particular worksheet/workbook has calculated.

Regards,
Dax.