Sheet to LOCK UP -- While Calculating

[asingh]

Hi,

I have an Excel Spreadsheet, which has two (2) tabs. Each tab has around 3000 Rows * 40 Columns of extensive formulas. [DBASE, Vlookups, Array Functions, CSE Functions, Index Calculations]. When ever a user applies a filter to the columns the sheet obviously re--calculates, and it takes quite a while. At times before the sheet reaches 100% calcuation status, the users click on the sheet or press ESCAPE, and the sheet does not calculate the whole way. And I get a FLOOD [believe me many...!] of mails saying "My values are incorrect, something is wrong with the sheet". I advised them to use F9 --- to force calculations, but they forget or are too impatient.

What I want to do is LOCK UP the sheet for user interaction until the calculations are complete. So ideally a programe flow would be like this:

Calculations Start ----- Sheet LOCKS UP ----- Calculations Reach 100% ---- Sheet OPENS UP for user Interaction.

I am not sure how to TRAP the calculation event, and then lock up the sheet...any ideas..............! [I am tired of receiving complaints...........!]

thanks a lot,

regards,

asingh

[Cyberdude]

asingh, I don't have a solution (regretably), but I have a gut feeling that one of the many, many events can come to your rescue ("MouseDown"??).

You might consider adding a progress bar to help the user understand what's happening. Just a thought.

Sid

[asingh]

Yes, plugging in a Progress Bar running on a modal form would solve the issue. But how do I get it to fire only when calculations start..and end when the calculations are over........!!!

[Simon Lloyd]

You could always change the Cursor by using
Application.Cursor = xlWait

Regards,
Simon