Creating a manual calc control

TSoren23

New Member
Joined
Sep 11, 2006
Messages
16
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?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

these lines speak for themselves :)
Code:
Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic
kind regards,
Erik
 

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
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
 

Forum statistics

Threads
1,136,272
Messages
5,674,749
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top