VBA - Prevent Excel 2007 from showing a defined names message box?

Posted by John M on Stack Overflow See other posts from Stack Overflow or by John M
Published on 2010-05-05T14:36:38Z Indexed on 2010/05/05 14:48 UTC
Read the original article Hit count: 169

Filed under:
|
|

I am working on a Excel 2007 workbook that will contain a macro to save the current sheet (a template) as

  1. a PDF file (no problem)
  2. 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

© Stack Overflow or respective owner

Related posts about excel-vba

Related posts about vba