I have some (many) equations in a spreadsheet. I have to change a
cell in the spreadsheet, which affects all the calculations, until
certain conditions are met. I check these conditions and change the
cell using VBA code.

My question: How can I be certain that calculations are completed,
before I change the cell value, which causes recalculation?

Please note that I cannot implement the formulae in VBA code, due to
user requirements.

TIA, Alan

You could put a message box at the end of your code, like this :

n = MsgBox("Calculations completed !",vbOkOnly)

--
If the post is helpful, please consider donating something to an animal
charity on my behalf.


"Alan" wrote:

> I have some (many) equations in a spreadsheet. I have to change a
> cell in the spreadsheet, which affects all the calculations, until
> certain conditions are met. I check these conditions and change the
> cell using VBA code.
>
> My question: How can I be certain that calculations are completed,
> before I change the cell value, which causes recalculation?
>
> Please note that I cannot implement the formulae in VBA code, due to
> user requirements.
>
> TIA, Alan
>
> .
>

Paul,

Thanks for the suggestion, but I need to detect this in the VBA code,
not notify the user. In fact, it would be a nuisance to the user in
some cases.

Alan

"Alan" <(E-Mail Removed)> wrote:
> My question: How can I be certain that calculations
> are completed, before I change the cell value, which
> causes recalculation?

I would use the Worksheet_Calculate (in a particular sheet object) or
Workbook_SheetCalculate (in the ThisWorkbook object) event macro, depending
on your requirements. In VBA Help, enter calculate and sheetcalculate to
descriptions.

Be sure to set Application.EnableEvents = False within the event macro,
since you indicate that you will make changes that cause recalculation. Be
sure to use On Error to ensure that you set Application.EnableEvents = True
before exiting. Other precautions might apply.


----- original message -----

"Alan" <(E-Mail Removed)> wrote in message
news:6f164237-6e33-4a1b-9d9d-(E-Mail Removed)...
>I have some (many) equations in a spreadsheet. I have to change a
> cell in the spreadsheet, which affects all the calculations, until
> certain conditions are met. I check these conditions and change the
> cell using VBA code.
>
> My question: How can I be certain that calculations are completed,
> before I change the cell value, which causes recalculation?
>
> Please note that I cannot implement the formulae in VBA code, due to
> user requirements.
>
> TIA, Alan
>