Worksheet calculate Event - Macro not working

[TG2812]

Hello, I have a pretty big file to run and attempting to have a "please wait" pop up windows whenever the sheet recalculate. However, the code I pasted below does not seem to work. When I see "Calculating ..4 processors" in the bottom right of the page, the pop up window and worst the code is not executing.

Is there something I did wrong?

Thanks in advance for your help!

------------------------------------------


Private Sub Worksheet_Calculate()
UserForm1.Show False
If Not Application.CalculationState = xlDone Then
DoEvents
End If

UserForm1.Hide

Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
End Sub

 

[mole999]

what happens with

Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True

 

[TG2812]

Unfortunately it does not fix the issue. This is what I tried:

---------------------------------------------------------

[I]Private Sub Worksheet_Calculate()[/I]

[COLOR=#ff0000][I]Application.Calculation = xlCalculationAutomatic[/I][/COLOR]
[COLOR=#ff0000][I]Application.DisplayAlerts = True[/I][/COLOR]

[I]UserForm1.Show False[/I]
[I]If Not Application.CalculationState = xlDone Then[/I]
[I]            DoEvents[/I]
[I]        End If[/I]

[I]        UserForm1.Hide[/I]

[I]Application.Calculation = xlCalculationManual[/I]

[I]End Sub

[/I]

 

[mole999]

if you are doing nothing else when you finish you subs, mainly you want to make calculations automatic again

 

[TG2812]

I turned the calculation into manual as my file is extremely heavy. However, I do not understand why my userform is not popping up when the worksheet is calculating..

 

[TG2812]

Any ideas?

 

[Domenic]

Actually, I think what happens is that it does indeed get triggered. The problem is that it only gets triggered after the worksheet calculations finish taking place. As a result, by the time the event is executed, the calculation state is done, so the userform gets shown and hidden so quick that you either hardly notice it or don't notice it at all. It looks like this will remain true with other events, such as the Change event.

 

[TG2812]

Domenic, your understanding is correct. I ran the macro step by step and this is exactly what happened. Are there any possibilities to remedy this situation?

 

[Domenic]

I tried setting the calculation mode to manual, and assigning a macro to a button on the worksheet to show the userform, re-calculate, etc. Unfortunately, Application.CalculationState always returns xlPending, so I was unsuccessful.

The only thing I can suggest is to make the worksheet itself more efficient...

1) Avoid using array formulas. In some cases, a regular formula can be used instead. If not, try using helper columns to achieve the desired results.

2) Avoid using whole column references. Instead, use exact ranges. However, if the range will change over time, either convert your data into a Table (Ribbon >> Insert tab >> Tables group >> Table) or use dynamic named ranges. In either case, the range will automatically adjust as data is added or removed.

3) Alternatively, try using a pivot table to summerize your data. Pivot tables are usually more efficient.

Also, you may find the following link helpful...

http://www.decisionmodels.com/index.htm

 

[Jaafar Tribak]

Are you calculating the worksheet using F9 or via clicking a button or ... ?