Userform: manual calculation mode makes code running very slow. How can I avoid the same speed losss when turning automatic again?

[Jamualson]

I have a subrutine which runs when a button is pressed on a userform. However it was very slow - 5-10 sec. Then I turned off calculation mode (manual) and speed became much better (0.5-1 sec)

However, when at the end of the sub I activate again the automatic calculation mode --> it takes 3-4 secs and until the calculation is finished I can not interact with the userform.

How could I improve this? I am thinking about turning back automatic calculation mode not at the end of the sub, but when the userform is quitted. So until that - no matter how many times my sub runs - it will be fast. How is this idea? Is it has down sides?

And if this is a good idea, how could I run the Application.Calculation = xlCalculationManual command when userform is about to be closed? I did not find any example how to attach this to the close of a userform.

Thank you very much for your idea, have a nice day

 

[ranman256]

in the form terminate event:
dbl-click the form
select the terminate event, (right event combo box)

Private Sub UserForm_Terminate()
Application.Calculation = xlCalculationManual
End Sub

 

[Jamualson]

in the form terminate event:
dbl-click the form
select the terminate event, (right event combo box)

Private Sub UserForm_Terminate()
Application.Calculation = xlCalculationManual
End Sub

Thank you very much