Creating a manual calc control

[TSoren23]

Hi,
I have a user form that enters data into a workbook. With all the calc,vlookup, etc in the sheets, there is a delay when I send the data from the user form to the worksheets.
I would like to turn off the auto calc function in my workbook and then only calc when I close the user form. How can I do this using vba?

 

[erik.van.geit]

Hi,

these lines speak for themselves

Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

kind regards,
Erik

 

[sunnyland]

Hello,

You could try using, this will calculate after closure of the form but will still leave calculation to manual, if you want to go back to automatic add :
Application.Calculation = xlCalculationAutomatic
after application.calculate

Application.calculate will do :

Calculates all open workbooks, a specific worksheet in a workbook, or a specified range of cells on a worksheet, as shown in the following table.

To calculate Follow this example
All open workbooks Application.Calculate (or just Calculate)
A specific worksheet Worksheets(1).Calculate
A specified range Worksheets(1).Rows(2).Calculate

So if it takes too much processing to recalculate everything of if your form is only changing data on 1 worksheet then use the underline version
------------------------------------------------------------



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


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
application.calculate
End Sub