Excel display box while calculating cells...

[zahead31]

I am using Excel 2003 with a custom formulas that came as a part of an Excel Add-in. These formulas query a SQL database on our server and return the result in the cell.

When I create a spreadsheet that uses more than about 20 of these formulas in different cells, the sheet will take anywhere from 30 to 60 seconds to calculate the entire sheet. The problem that I run into is that many users are impatient and will start clicking on the cells, which will stop the sheet from calculating (I have Excel set up to calculate manually).

Is there a way to get a window to pop up with a message like "Calculating Cells, Please wait..." whenever Excel is calculating the cells?

Thanks much!
Regards,

Matt

 

[Fenrirshowl]

I think you can do it using VBA, so goto Tools menu / Macros/Visual Basic Editor. Set up a userform with the desired message on. I will assume this is called userform1.

Right-click the userform from the projects explorer window and view the code. Change the code window from "General" to "Userform". In the right drop down next to it select "Activate". Next enter the following code...

Private Sub UserForm_Activate()
Application.ScreenUpdating = False
Me.Hide
Application.Calculate
Application.ScreenUpdating = True
End Sub

Then go back to the spreadsheet. Right click the sheet tab and select view code. Again adjust the dropdowns, but this time to show "Worksheet" and "Calculate". Now add the following code:

Private Sub Worksheet_Calculate()
UserForm1.Show
End Sub

I think that should do it.....maybe.

 

[zahead31]

Fenrirshowl,

Thank you for the quick response! I will try this code and let you know my results.

Thanks again,
Matt

 

[zahead31]

I entered the code just how you stated. When the sheet goes to calculate, the userform does not appear until after the sheet has finished calculating. As soon as the userform appears, I get a Run-time error '28': Out of stack space.

Any idea what that means, or how to get the userform to show while the is calcuating cells?

Thanks!

 

[PHV]

FYI, the Worksheet_Calculate event fires AFTER the calculation is done ...

 

[Fenrirshowl]

Sorry Matt, I didn't realise the Calculation Event fires after the sheet has calculated - this critical point from PHV, which is good to know, makes my earlier advice useless.

I suppose you might be able to add a button whose code activates the userform rather than the calculate event. Don't know if this would be any use?

 

[zahead31]

That is what I was thinking. Your code and thought process has helped me out a lot. Now, it seems I just need to get a little creative to accomplish my task!

Thank you Fenrirshowl and PHV for your helpful posts!

 

[SkipVought]

Hi,

You might check out the AfterRefresh Event

http://support.microsoft.com/kb/213187

Skip,

_[red][/red]