message during sheet calculation

Question

Hi,

Is there a way to display a message while sheet calculating very high volume of formulas?

I know it displays in status bar but I want to display it in a message box as well.

Thanks in advance,

Hari

Answer 1

Are you letting Excel control the recalculating?  That is, is the calculation mode set to Automatic?

If it is, then this is very difficult to do because while we can tell when calculating starts, there's nothing to tell us when it is done.

But if you have the workbooks calculation mode set to manual and are using a macro to have the recalculation done, then you could probably do it with a UserForm shown in non-modal state.  Note that using a macro is not the same as pressing [F9] or [Shift]+[F9].

And if you are using a macro, then you might speed it up enough to not need the message or form by adding one line of code before you start the .Calculate operation:

Application.ScreenUpdating = False

that one line of code can sometimes improve performance dramatically.  You don't even have to worry about setting it back to = True because Excel will do that when it gets to the End Sub statement or otherwise exits the macro.

Answer 2

You can't display a message box while the sheet is calculating. The status bar is be best way, although the status bar tends to also include a progress bar for when calculations take a long time. 

You could have code that displays another sheet, does calculations, and then goes back to the original sheet. The sheet you display could say the sheet is calculating. But..really..better to keep it simple.

tod

Answer 3

Yes JLLatham,

It is the case when calculation is Automatic.

I am new in vba & don't know if there is a way to read status bar value & display it in a message box or even in cell.

i thought it is possible. anyway thank you  JLLatham for your reply.

Also thank you Tod.

Regards,

Hari