Solved: automatic FormulaCalculation Option using VBA

[Beatrix]

Hi Everyone ,

I have below script which produces templates. Formula Calculation option is Automatic in main workbook however it goes manual in templates. I need to make formula calculation Automatic as default. Would you help me to edit the script to be able to do this please?

Cheers
Yeliz

Sub make_files()

Dim wb As Workbook, wbCode As Workbook, wsUsers As Worksheet
Dim LR As Long
Dim User_ID As String, User_Name As String
Const fPath As String = "C:\Excel VBA\master\" 'change to your path

With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
Calc = .Calculation
.Calculation = xlCalculationAutomatic
End With

Set wbCode = ThisWorkbook
Set wsUsers = wbCode.Worksheets("users")

With wsUsers
LR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
For i = 2 To LR
User_ID = .Range("A" & i).Value
User_Name = .Range("B" & i).Value
Set wb = Workbooks.Open(fPath & "master.xls")
wb.Worksheets("H-POD").Range("M8") = User_Name
wb.SaveAs (fPath & "H-POD_" & User_ID & "_v02"), FileFormat:=17
wb.Close False
Next
End With

With Application
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
.Calculation = Calc
End With

End Sub

[Teeroy]

.Calculation is an Application property, not a Workbook property so the results you are getting are probably not consistent (dependant on the Application property of each computer). You could set the .Calculation to xlCalculationAutomatic in the workbook_open event of the master spreadsheet which would carry through to each of the templates, forcing the property value whenever they are opened.

[mancubus]

.Calculation is an Application property, not a Workbook property so the results you are getting are probably not consistent (dependant on the Application property of each computer). You could set the .Calculation to xlCalculationAutomatic in the workbook_open event of the master spreadsheet which would carry through to each of the templates, forcing the property value whenever they are opened.

and macros must be enabled by all users before opening the template.


goes to ThisworkBook code module in master.xls:


Private Sub Workbook_Open()
Application.Calculation = xlCalculationAutomatic
End Sub

[mancubus]

http://www.vbaexpress.com/kb/getarticle.php?kb_id=578

this KB article shows how to force users enable acros for Excel 2003.
download and examine the sample file.

[Beatrix]

Thanks very much guys for the replies..Much appreciated!!

Cheers
Yeliz

[mancubus]

wellcome.