1. Open the VBA editor by hitting ALT+F11.
2. Navigate to your workbook in the project explorer in the left pane and click the name of your workbook to activate it.
3. Click INSERT in the menu bar at the top of the VBA window and choose UserForm.
4. Add a text box to your userform that says something like "Please wait while the calculations are being performed. This window will close automatically when it is OK to proceed". This can really be anything you'd like it to say.
5. Once you have created the form, in the project explorer on the left, navigate to your workbook and on the "ThisWorkbook" object, right click and select "View Code"
6. In this window that opens up, paste the following code:
Code:
Private Sub Workbook_Open()
Dim MyArray As Variant
Dim wsht As Worksheet
Set MyArray = Sheets(Array("Sheet1", "Sheet2")) 'modify this based on the name of the sheets you need to disable auto calculation for, if you need to only do it to 1 sheet, that will be ok too.
For Each wsht In MyArray
With wsht
Application.Calculation = xlCalculationManual
End With
Next wsht
End Sub
7. Modify that code based on the sheet names of the sheets you need to disable calculation for. See my comment in the code itself.
8. Click INSERT again in the menu and choose MODULE.
9. In the new window that opens, paste this code:
Code:
'---------------------------------------------------------------------------------------' Procedure : Calc_and_ShowForm
' Author : Michael Barry
' Date : 7/7/2015
'
' Purpose : This procedure will show a form which instructs the user to please wait while calculations are being performed.
'
' Instructions : Run this via a button to perform calculation. After it completes, calculation will be turned back to manual.
'---------------------------------------------------------------------------------------
'
Sub Calc_and_ShowForm()
UserForm1.Show False
Application.Wait (Now + TimeValue("0:00:01"))
Application.Calculate
If Not Application.CalculationState = xlDone Then
DoEvents
End If
UserForm1.Hide
Application.Calculation = xlCalculationManual
End Sub
10. Add a button to your worksheet and assign this macro to it (Calc_and_ShowForm).
Now, when you open the workbook, auto calc will be disabled on the worksheet. You will initiate a calculation by clicking the button, at which point your form will pop up, tell the user the calculations are being performed (which will be happening in the background) and once it has completed, the form will go away and calculation will be set back to manual.
I have included a link to a file that has all of this done already if it helps you (as you may not be familiar at all with VBA and even with the instructions above it may be difficult). Not sure if it will be easy for you to import your existing data into this workbook or not though.
https://www.dropbox.com/s/s8plhavbgwzpt6j/Sheripres_FormWhileCalculating.xlsm?dl=0
