PDA

View Full Version : calculate only when macro has been enable.



VISHAL120
04-03-2013, 08:05 AM
Hi ,

I am trying work out with the attached files where the calculation has been set to manual.

And the file shall calculate only when the macro has been enable but not when disable as besides this there is a lot of check being done through VBA and if user click on the disable button and then the calculation is done it may have big different on the stock values.

here is the code am trying since long :

Sub CALCULATION()


Worksheets("HANDFLAT").Activate

' With Application
' .CALCULATIONfull
' .Iteration = True
' .CalculateBeforeSave = True
' End With
Application.CalculateFullRebuild

Application.CALCULATION = xlCalculationManual

End Sub

but the code seems not be working.

I am also attaching part of the file to have an idea.

many thanks for helping in advance.


:banghead::banghead:

mdmackillop
04-04-2013, 04:37 PM
The Activate line fails with me. try
Worksheets("HANDFLAT").Select

VISHAL120
04-04-2013, 09:47 PM
hi ,

thanks it works on the handflat sheets.

Can you help me further on how can i do it also for other sheets. Shall i code for all the sheets seperately.

as the workbook will have approx. 14 sheets for control.

thanks again for your help.

SamT
04-05-2013, 08:59 AM
Sub Application_WindowActivate(ByVal Wb As Excel.Workbook, ByVal Wn As Excel.Window)
Wb.Application.CALCULATION = xlCalculationManual
End Sub

VISHAL120
04-10-2013, 03:33 AM
hi Sam,
Sorry for the lateness

I have not well understand on how to use the above code well.

can you please explain me.

many thanks.

SamT
04-10-2013, 07:19 AM
Vishal,

You want workbook to calculate only when your Sub CALCULATION() Runs.

Put this sub in ThisWorkbook CodePane for every workbook you want to force CalculationManual

Sub Application_WindowActivate(ByVal Wb As Excel.Workbook, ByVal Wn As Excel.Window)
Wb.Application.CALCULATION = xlCalculationManual
End Sub

VISHAL120
04-10-2013, 10:58 PM
ok thanks i try and revert