Calculations off while Form is showing

[MarkBoy]

Hi,

I have a fairly sizable spreadsheet, and the whole thing grinds to a halt if calculations are on while forms are running. As such, I want to turn the calculations to manual while the forms are active, but turn them back to automatic when the form is closed.

Now, obviously:

Private Sub UserForm_Activate()
Application.Calculation = xlManual
End Sub

does the job of switching it to Manual, and the spreadsheet runs like a dream. The problem, however, is that the form is always closed by clicking the top-right-hand "X". And that would seem to NOT activate the following code:

Private Sub UserForm_Deactivate()
Application.Calculation = xlAutomatic
End Sub

Is there any way to trigger a command on a form being closed like that? Any other suggestions of devious ways of dealing with this that I haven't thought of?

 

[RoryA]

You could use the Userform_Terminate event.

 

[MarkBoy]

Thanks! For some reason, my searching never turned up that event!

That has sped up my spreadsheet dramatically (like a previously 20sec subroutine is now instanteous), now I just have to figure out a way to make it faster when calculations are actually active!

 

[MarkBoy]

Ok, have encountered my next difficulty. All my searching has said that the Userform_Activate event should occur every time a form gains focus. Each of my forms calls other forms, and now each of them switches calculation to Manual on activation and switches calculation to Automatic on termination.

The problem is that when I close one form, and go back to the one I was previously using, calculation is definitely back to Automatic. I'm resigned to the fact that it will recalculate when shifting between forms, but it is a huge pain if it continues to calculate while using the first form.

Any suggestions about how this might be made to work?

 

[dafan]

Use the Userform_Click event so when focus goes back to a userform calculations are switched off?

Isn't one of the Userforms the 'master' form from where the user navigates to all other userforms? If so, you could use that _Terminate event only to switch back on the calculations.

 

[RoryA]

How are you switching from form to form (switching back and forth between two forms can be problematic, particularly if you use the Userform1.show syntax and Unload Me statements in the forms. I agree with Stefan that your simplest bet would be to have a master form if possible (like a switchboard) and have that turn calculation off when loaded.