VBA - Prevent Excel 2007 from showing a defined names message box?
- by John M
I am working on a Excel 2007 workbook that will contain a macro to save the current sheet (a template) as
a PDF file (no problem)
a Excel 97-2003 file (problem)
When saving the Excel file a messagebox appears asking about
"Defined names of formulas in this workbook may display different values when they are recalculated...Do you want Excel to recalculate all formulas when this workbook is opened?".
The user can then select Yes/No and then the file will save.
How do I disable the messagebox from appearing?
The default answer would be 'No'.
My code for saving:
Sub saveAs_97_2003_Workbook(tempFilePath As String, tempFileName As String)
Dim Destwb As Workbook
Dim SaveFormat As Long
'Remember the users setting
SaveFormat = Application.DefaultSaveFormat
'Set it to the 97-2003 file format
Application.DefaultSaveFormat = 56
ActiveSheet.Copy
Set Destwb = ActiveWorkbook
Destwb.CheckCompatibility = False
With Destwb
.SaveAs tempFilePath & tempFileName & ".xls", FileFormat:=56
.Close SaveChanges:=False
End With
'Set DefaultSaveFormat back to the users setting
Application.DefaultSaveFormat = SaveFormat
End Sub
