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

3
votes

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
1
vbaexcel

1 Answers

2
votes

Try putting this around the With, End With:

Application.DisplayAlerts = False

With Destwb
    ...
End With

Application.DisplayAlerts = True

Should suppress the message, but not sure if it'll default the way you want.